Introduction to SQL

What is Structured Query Language (SQL)?

Structured Query Language (SQL) was developed at IBM San Jose Research Laboratory as a part of System R project. It is a declarative query language for querying a relational database. It also includes features for defining the structure of the data, for inserting and modifying data in the database, and for specifying security constraints. It is relational complete (it supports all six core relational algebra operations). SQL commands can be classified into three groups out of which we are going to practice commands that query a database, called Data Manipulation Language (DML).

Activity Outcomes:

After performing this lab students should be able to:

1. Design SQL queries to retrieve data using SELECT clause and various associated operators.

2. Translate relational algebra expressions to SQL queries.

3. Execute SQL queries over MySQL using MySQL Workbench. Instructor Note:
As pre-lab activity, attend theory class.

Tools/Software Requirement

•  MySQL Community Server 5.6
• MySQL Workbench 6.1
• Sakila Database

Lab Activities:

Activity 1:

1. This lab assumes that MySQL Community Server is running and Sakila database has been loaded using MySQL Workbench.

2. Open MySQL Workbench and open the default connection instance.

3. A new query window would open from where you can write and execute queries.

4. You can save the query file and can also add comments using # symbol.

5. On executing queries, results are displayed in the lower part of the screen.

6. Error or success messages are displayed in action output pane at the bottom.

7. Try running few SQL queries modeled during the lectures to get it going.

8. Continue playing with the Workbench and SQL queries till you are comfortable with the querying mechanism and have learnt the shortcuts to execute queries.

Solution:

Activity 2:

1. Write SQL queries for the following information needs. You should execute your attempt and make necessary corrections if needed.

2. Information needs:

a. Retrieve first names of all actors. [2]

b. Retrieve all payments over and above $10 made during 14-22 August 2005. [3]

c. Find all films of more than two and half hour length and not rated PG-13. [3]

d. List id, title, rental rate, and replacement cost of all films below $1 rental value. [3]
16
e. Find id and email of all customers whose first name is SARAH. [2]

f. Retrieve all addresses from Ishikawa, Hiroshima, or Osaka districts [2]

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.