School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 6: Tute/Lab – SQL Programming
Semester 1 2020
1 Objective
The objectives of this tute/lab session are:
• Learn to use SQLite Studio for SQL programming;
• Develop basic SQL programming skills.
1.1 PreparationTasks
You must already have used SQLite Studio in your previous lab/tute sessions. If not, redo your week 2 lab sheet, which describes step-by-step instructions on using SQLite Studio in the university lab setup.
In this lab, we use Movies Database, which is available as .db file (Movies.db) within Canvas (ModulesàResources: Sample Databases and Tools).
1. On the dialog box, choose the Movies.db file you downloaded.
2. After you add the database, it should appear on the left-hand pane. Double-click on the
database name and then, it will be opened.
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020
School/Department/Area Page 1 of 9
3. If you successfully added and then opened the database, it should appear as opened on the left-hand pane and added to the database chooser at the top of the SQL editor screen.
If you cannot see “Movies” on the database chooser, You may have to explicitly open it, by Clicking “ Connect to the Database” button at top left corner, or menu options: Databaseà Connect to the database. If the connection did not work, you must have made some errors. Revisit the previous steps.
2
2.1 UsingSQLiteStudio
SQL Activities
In this activity, you explore the interface and be familiarised with essential tasks, such as running a simple query, running a SQL script which consist of more than one SQL query, saving results, and saving SQL scripts.
2.1.1 Running a simple SQL query.
Type a simple SQL query, such as:
Then, hit the “Execute Query” button – the blue arrow at the top of the editor screen.
If you have multiple databases connected into your session, make sure you have chosen the correct database on the Database Chooser (see the above screenshot).
2.1.2 Saving results to a .csv file
After running a query on SQL Developer, you may sometimes be required to save the results onto a CSV file. For example, if you wish to upload the results set onto a Excel spreadsheet (or a Google sheet, a mac Numbers) you can use this CSV file.
Step 1: Run the query to produce the required output.
SELECT *
FROM director
School/Department/Area
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 Page 2 of 9
Step 2: Click “Export Results” button.
Step 3: One the next Export Results dialog box, choose export type as “CSV” and specify a location (directory/filename) to save the CSV file.
Note that, you can save results in many file formats, including plain-text, CSV, and pdf formats.
A special export type is “SQL” type. If you choose this type, the results are exported as a SQL script containing a bunch of INSERT INTO
VALUES ; SQL statements. You can then use this file to populate another table (with the same schema as the result set).
2.1.3 Open an SQL script and run it.
In this activity, you learn how to run an SQL script you had previously saved, or downloaded from another source.
To do this activity, you need a sample SQL script. You may generate your own SQL script or download the “Sample-SQL-script1.sql” from the Canvas Module 6.
Step 1: Click on the “Load SQL from file” button on the top of the screen.
Step 2: On the Open File dialog box, choose the downloaded SQL file (e.g. Sample-SQL- script1.sql).
The SQL script will appear on SQL editor pane. Run it by highlighting it on the SQL editor window.
2.1.4 Save an SQL script
When you run some SQL statements over and over again, it is a good idea to save them as a .sql file. Then, you can re-run them whenever they are required, by following the steps in 2.1.4.
To save your SQL queries on a .sql file, click “Save SQL to file” button on the top of the screen.
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 3 of 9
3 SQL Exercises
The movies database is built to store information about movies, directors, and stars (i.e. actors and actresses) in a video store. In addition, it stores information on members (who got active memberships to borrow videos from the video store) and their borrowing transactions.
A sketchy ER diagram for the movies database is given below.
Director
1..1 Directs
0..N 0..N
MvNumb {PK} MvTitle YrMade MvType
Crit MPA A Noms Awrd
Movie
0..N MovStar
1..1
By 0..N
1..1
Includes 0..N
Member
MmbNumb {PK} MmbName MmbAddr MmbCity MmbSt NumRent Bonus
JoinDate
DirNumb {PK} DirName DirBorn DirDied
Star
StarNumb {PK} StarName BrthPlce StarBorn StarDied
Borrow
TxnNumb {PK} BorDte
3.1 Queriesusingasingletable
The following is the script of an imaginary scene associated with the video shop. You are required to provide the database assistance to Ben, the checkout clerk, by giving him the SQL command for each of the query required in the discussion. All of these queries can be answered with using a single table. As such, joining two or more tables is not an skill you develop in this exercise.
Jerry, a client (a member of the video rental shop) comes to the shop and is greeted by Ben.
Ben:
Good afternoon, I am Ben. May I help you, Sir?
Jerry:
Yes please. What are the movies available in your collection? Ben:
Just a second, I’ll check it in the database.
This question can be interpreted as “list all movies in the Movies table. Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thanks Ben. But that’s too much information. I am overwhelmed with information overload. Can you show me the movie titles, nothing else.
Ben:
Of course. That is easy.
In database terms, this question can be interpreted as “display the titles of all movies in the Movies table.” Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 4 of 9
That’s nice. Very impressive movies collection. By the way, how many movies are there in your collection?
Ben:
Let me count them on the screen, ….. , oh that’s too long, (and while scrolling down), I lost the count. I am thinking of a better way to this.
In database terms, this question can be interpreted as “count all the movies in the Movies table.” Write a SQL statement to assist Ben to obtain required data from the database.
Ben:
There are 24 movies in our collection. Are you interested in borrowing a few for tonight? Jerry:
I am interested in Horror movies. So, can you help me find some?
Ben:
Of course. Let me find some horror movies.
In our database schema, there is an attribute called “MvType” which stores movie type. What is required in database terms is to filter the Movies table on this attribute. Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thanks you. That’s nice. Out of these horror movies, are there any movies nominated for academy awards?
Ben:
I recall there were some very highly regarded horror movies. Let me find out.
In our database schema, there is also an attribute called “noms” which stores the number of nominations each of the film received. What is required in database terms is to filter the Movies table on this attribute in addition to the filtering on MvType. “noms” must be greater than 0. Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thanks you. I’ll borrow them. I might take a few comedies for my kids. Can you help find some. Ben:
I am here to help. Let me find out.
This is a tricky one. You have to interpret the client’s requirements carefully. He wants to borrow horror movies with nominations and comedy movies. However, when this generic requirement converted to database query terms, the results set should meet one of the two criteria: (1) good horror movies with nominations; or (2) comedy movies. The first condition itself consists with two components. So, the WHERE clause should contain three conditions, which are carefully ordered (using parenthesis if required). Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
While we are at it, I must admit that my kids are also very picky about movies. Have these comedies received any award nominations?
Ben:
Why not? Let’s try that.
You have to interpret this requirement differently. He wants to borrow horror movies or comedy movies, with those with award nominations. We must re-arrange the conditions by changing where the parenthesis are used. Here we first retrieve movies of either type and then see
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 5 of 9
if they have received any nominations. Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thanks. Having being nominated to an award is one thing, and receiving an award is definitely quite an achievement. What are the movies in your collection, which have received more than three Academy Awards?
Ben:
That’s easy to find.
This is in fact an easy question to interpret. What we have to look for is values greater than 3 in awrd attribute. Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
I vaguely remember there was an enthralling movie about the Nuremberg Trials. But, I cannot recall the exact title. Do you know what is it?
Ben:
Cannot remember from the top of my head. But, we can check the database.
This is a classic partial match query. The client knows a word (or a phrase) and requires to retrieve all matching rows. (If the result set is still too broad (lengthy) we may require to narrow down our partial match or add more conditions). The client knows that the word “Nuremberg” in the movie title, so we must use wildcard characters appropriately placed in the comparison string to be included in the LIKE condition within WHERE clause.
Write a SQL statement to assist Ben to obtain required data from the database.
Extension to this is that if you do not know the case of the data stored in the database (upper-case, lower-case, mixed-case). For example, Nuremberg can be stored as either Nuremberg or NUREMBERG or nuremberg.
In this case, your comparison string should be able to pick any of these three.
Write a SQL statement to assist Ben to obtain required data from the database, assuming Ben doesn’t know in which case the data are stored.
Hint: use OR operator.
Jerry:
Thanks Ben. You are very helpful. I’ll take these videos today. See you again next week. Ben: You are mostly welcome.
3.2 AdvancedActivity
After the client, Jerry, left, Ben was called by Anna, their store manager.
Anna:
Hi Ben, I heard that you are a database expert.
Ben:
I wouldn’t call myself a database expert. I can write simple SQL statements, though.
Anna:
That’s good. I have few questions about our clients.
I am concerned about how active our clients are. On average, how many rentals in total they have done?
Ben:
Let’s see if we can obtain these data.
School/Department/Area
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 Page 6 of 9
This requires the use of aggregate functions. We have already used one aggregate function – COUNT – to count rows in a result set. In this query, we have to average out a column that contain the total number of rentals each member has made. NumRent attribute holds these data. You are required to calculate the average of the numbers down that column.
Write a SQL statement to assist Ben to obtain required data from the database.
Anna:
Thanks Ben. That’s promising. We may start a promotional campaign to encourage our clients to rent more videos. I was thinking of having a free video day – clients can rent a video for free on their membership anniversary day. Can you prepare a report containing member names and their join dates?
Ben:
Easy. Here it is.
The Member table has joindate attribute. So, this is, in fact, an easy task. Write a SQL statement to assist Ben to obtain required data from the database.
Anna:
Sorry Ben, but, I do not like the date format you have in your report. Can you produce these dates in standard Australian date format?
Ben:
Yes, it is possible. Here the dates are listed in DD/MM/YYYY format.
Different database systems manage dates differently. So, Ben must know about date functions, date formatting, and internal date storage types for the database he uses.
In SQLite, dates are stored as either text, integer or real data. For more information on handling dates in SQLite, please refer to Module 6 additional slides (named “Dates in SQLite and Oracle”).
In Oracle, we can format the output whatever the style you wish, by changing the session settings. After changing these settings, it displays the dates in the desired format until you close the current session. The session variable NLS_DATE_FORMAT can be used to set the date format of the current session.
Similarly, we can use NLS_TIMESTAMP_FORMAT to change timestamp format.
Write a SQL statement to assist Ben to change the date format displayed on the current session.
Anna:
Thanks, I like the current look of it. Can you please add another column to display how long they are members with us.
Ben:
I think I know how to do this. Do you want to display it in days or years?
Anna:
Just show them both.
We can do simple arithmetic calculations (such as adding or subtracting dates) with DATE type data. However, the results may require some form of post-processing (such as changing the display format, rounding up or rounding down, etc).
Write a SQL statement to assist Ben to obtain required data from the database.
Anna: That’s great. But, isn’t it nice if you sorted these results. Say, sort on the alphabetical order of names.
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 7 of 9
Ben:
No problem.
This is an easy task. You simply add an ORDER BY clause. You will be surprised to know that even experienced database programmers incorrectly add a SORT BY clause!
Write a SQL statement to assist Ben to obtain required data from the database.
Anna:
Mmm, I think it is better to sort by the tenure (i.e. number of days since they joined) in the descending order. Can you fix it?
Ben:
That’s easy.
This is an easy task. You simply add an ORDER BY clause. But, remember to add DESC at the end.
Write a SQL statement to assist Ben to obtain required data from the database.
Anna:
That’s not bad. However, it is nice if you can change column headers to make them clear and meaningful. I am going to present this report to my colleagues.
Ben:
No worries. Do you like to call the columns, say Member Name, Date Joined and Number of days since joined?
Anna:
Yes, that will do.
It is always a good practice to rename column names with more readable, meaningful column headers. Use AS keyword to substitute column headers.
Write a SQL statement to assist Ben to obtain required data from the database.
Anna:
Thanks. Have a nice day. Ben:
You are welcome.
School/Department/Area
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 Page 8 of 9
4 Extension Work 4.1 GroupActivity
If you complete all the activities in Section 3, form yourselves into groups of 4 – 5 around the table. Conferring with your colleagues, write up a new set of questions semantically similar to the above questions Ben was assisting with.
Then, swap your questionnaire with the adjoining group and write down SQL queries to answer the questions you received from them.
4.2 CreativeTask
Be a creative script writer. Extend your imagination and construct similar dialogues associated with the video shop and the movies database. Such dialogue should lead to querying the movies database, similar to above dialogues, and attempt to construct SQL commands for each query.
You are most welcome to post your creative dialogues in ‘General Discussions’ forum (without SQL queries) and invite other students to be your Database Assistant!
Document: Week 6 – SQL Tute 1 V2.0.docx Author: Santha Sumanasekara Save Date: 19/03/202020 School/Department/Area Page 9 of 9