SQL Questions

By P. Conrad, for CISC474, Spring 2007

  1. Explain the concepts of database, table, row, query, and schema. Illustrate your explanation with examples.
  2. What is a primary key?
  3. Give the syntax needed to create a table that can represent all the students in this course, with first name, last name, group number, and UD email address. (Store the email address without the @udel.edu part.) Let email address be the primary key.
  4. Give the SQL syntax to insert real values into that table for five students.
  5. Suppose that each group decides to give itself a nickname ("Webmeisters", "CrazyFools", "CoffeeFiends", "CoderDoods", etc.). Give the syntax to create that table, and populate it with five example values.
  6. Give the SQL syntax for a query that will combine the information from the two tables you created as answers to the questions above, so that you can show each student's first and last name along with the group name (but not the group number) of the group to which that student belongs.
  7. (continuation of previous question) What is the technical term used for the type of query you did for the previous question—i.e. a query that combines information from two different tables?
  8. What are the SQL keywords used to (a) remove a table from the database, (b) change the schema for a table?
  9. What is the syntax to change your password in MySQL? Why do you need to know "what machine" you'll be working on when you go to change your password?
  10. When using MySQL with PHP or JSP web apps, sometimes you have to use the OLD_PASSWORD hack. What is this hack, and under what circumstances is it required?