SELECT Query Modifiers
WHERE clause and query modifiers (ORDER BY and LIMIT) are critical to retrieve required information for a given information need. These clauses form very commonly used in SELECT type of queries
Activity Outcomes:
After performing this lab students should be able to:
- Design SQL queries to retrieve data using WHERE clause and various associated query modifiers.
Tools/Software Requirement
- MySQL Community Server 6
- MySQL Workbench 1
- Sakila Database
Lab Activities:
Activity 1:
- This lab assumes that MySQL Community Server is running and Sakila database has been loaded using MySQL
- Open MySQL Workbench and open the default connection
- A new query window would open from where you can write and execute
- You can save the query file and can also add comments using #
- On executing queries, results are displayed in the lower part of the
- Error or success messages are displayed in action output pane at the
Solution:
Activity 2:
- Write SQL queries for the following information needs. You should execute your attempt and make necessary corrections if
- Information needs:
- A sorted list of country names ending with “tan” such as Pakistan. [2]
- What are the names of all the languages in the database (sorted alphabetically)? [2]
- Find the full names (by concatenating first and last) of actors with “SON” in their last name, ordered by their first name. [3]
- Find all the addresses where the postal code is missing (empty or null), and return these district-sorted. [3]
- Return the sorted list of film titles given the film involves a “crocodile” as well as a “monkey” (Hint: film_text). [2]
- Find id, title, rental rate and replacement rate of 10 shortest films. [3]
Solution:
Home Activities:
- Answer questions from the task. These questions assume Sakila database hasn’t changed.
Lab Assignment and Viva voce:
Deliverable:
Submit a PDF document including the SQL queries to answer above-mentioned information needs as well as snapshot of their outcome when executed over MySQL using the Workbench.