Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: SQL statements

  1. #1
    Join Date
    Apr 2011
    Posts
    17

    Unanswered: SQL statements

    I've just started learning mySQL and just wanted to check if my answers are correct.

    There are two tables Contacts and Departments. Create the following SQL queries.

    (i) Find all contacts who surname begins with D.

    SELECT * FROM contacts where surname like "d*"

    (ii) Find all contacts whose name is john and is older than 45.

    SELECT * FROM contacts where firstname like "john" and where age > 45

    (iii) Delete all departments whose department head is Dana Black
    DELETE FROM departments
    WHERE DeptHead='Dana Black'

    (iv) Display all contacts that belong to the department description is maths
    SELECT * FROM contacts WHERE Dept_Description like "maths"

    (v) Add the following contact Jim White, age 33, phone: 123456789
    INSERT INTO contacts
    VALUES ('Jim', 'White', '33', '123456789')

    (vi) Write an SQL statement to create the contacts table
    CREATE TABLE contacts

    It would be great if someone could help me out here or confirm my answers are correct. Thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You could always try running the SQL and seeing if it does what you expect.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gary223 View Post
    It would be great if someone could help me out here or confirm my answers are correct. Thanks.
    i can confirm that i, ii, iv, v, and vi are wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Come on Rudy you are a little harsh with the last one he is half right.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by it-iss.com View Post
    Come on Rudy you are a little harsh with the last one he is half right.
    no harsher than mysql would be

    try running that and see if you get half an error message

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You are right. Just tried it.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by gary223 View Post
    It would be great if someone could help me out here or confirm my answers are correct
    Gary - is there a particular reason you can't just type in the SQL and try running it? MySQL will tell you if you have any syntax errors and if you compare the rows you get with what those you expected (this is called testing) then you'll know if you're correct. If there's something you don't understand at this point then we'll be more than happy to help out.

  8. #8
    Join Date
    Apr 2011
    Posts
    17
    I don't have the software on this computer to test it out, so its difficult.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gary223 View Post
    I don't have the software on this computer to test it out, so its difficult.
    can you get to a computer that does have the software before the assignment is due?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2011
    Posts
    17
    No, I'm on my Easter holidays now and the exam is in a couple of days when we're back in. I'm kind of panicking a bit now.

  11. #11
    Join Date
    Apr 2011
    Posts
    17
    Just noticed I left out the semi-colons on the answers above.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gary223 View Post
    Just noticed I left out the semi-colons on the answers above.
    they are optional
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2011
    Posts
    17
    Could you correct my above answers so I can take a look at some similar questions and try and do those? It would be a big help.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gary223 View Post
    Could you correct my above answers so I can take a look at some similar questions and try and do those? It would be a big help.
    sorry, that's not how we handle homework assignments in this forum

    you have to do the work, we will only guide you

    i'd be happy to give you hints, though

    for question (i), you have the wrong wildcard character, and the string delimiters, while they do work in mysql, do not conform to the sql standard and will fail in other databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2011
    Posts
    17
    I was taught in class to use the '*', I don't recall being told how to do it any other way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •