Skip to content

Latest commit

 

History

History
288 lines (212 loc) · 7.49 KB

SQL_Review.org

File metadata and controls

288 lines (212 loc) · 7.49 KB
  • Copying

    This is an extension of CodeClan’s fringeshows.md SQL Review teaching material.

    Addtional Copyright © 2016 mle

SQL Questions

First create a database called fringe_shows

#terminal
psql
create database fringe_shows;
\q

Populate the data using the script - fringeshows.sql

#terminal
psql -d fringe_shows -f fringeshows.sql

Using the SQL Database file above (fringeshows.sql) as the source of data, generate SQL commands the answer the following questions. Paste both the SQL command and the result under the questions below.

Revision of concepts that we’ve learnt in SQL today

Select the names of all users.

SELECT * FROM users;
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 4
INSERT 0 18
INSERT 0 1
INSERT 0 1
idname
1Rick Henry
2Jay Chetty
3Keith Douglas
4Valerie Gibson
5Steven Meiklejohn
6Zak Buys
7Harry Swan
8Henry Dashwood
9Seumus Blair
10David MacKintosh
11Allan Harrison
12Chris Wood
13Adam Pinner
14Jeffrey Lloyd
15Alistair MacKay
16Andrew Craib
17Rebecca Kelly
18Hamish Edmondson
19Wojciech Tartanus
20Donald Lessels
21Aidan Pinkman
22Lewis Johnston

Select the names of all shows that cost less than £15.

select name from shows where price < 15 ;
name
Le Haggis
Paul Dabek Mischief
Best of Burlesque
Two become One
Urinetown
Two girls, one cup of comedy

Insert a user with the name “Val Gibson” into the users table.

insert into users (name) values ('Val Gibson');
INSERT 0 1

Select the id of the user with your name.

select id from users where name = 'Hamish Edmondson';
id
18

Insert a record that Val Gibson wants to attend the show “Two girls, one cup of comedy”.

insert into users (name) values ('Val Gibson');
INSERT INTO "shows_users" (show_id, user_id) VALUES ((SELECT id from shows where name ='Two girls, one cup of comedy'), (SELECT id from users where name ='Val Gibson'));
INSERT 0 1
INSERT 0 1

Updates the name of the “Val Gibson” user to be “Valerie Gibson”.

UPDATE users SET name='Valerie Gibson' WHERE (name='Val Gibson');
UPDATE 1

Deletes the user with the name ‘Valerie Gibson’.

DELETE from users where name='Valerie Gibson';
DELETE 1

Deletes the shows for the user you just deleted.

DELETE 
FROM shows_users 
WHERE NOT EXISTS (
    SELECT * 
    FROM users WHERE id = shows_users.user_id);
DELETE 3

(‘3’ because I was fannying about)

More Complex Queries.

You will need to go and find out about aggregate funcions in SQL to answer some of the next questions.

Ordering:

Select the names and prices of all shows, ordered by price in ascending order.

select (name,price) from shows order by price;
row
(“Two girls, one cup of comedy”,6.00)
(“Best of Burlesque”,7.99)
(“Two become One”,8.50)
(Urinetown,8.50)
(“Paul Dabek Mischief “,12.99)
(“Le Haggis”,12.99)
(“Joe Stilgoe: Songs on Film – The Sequel”,16.50)
(“Game of Thrones - The Musical”,16.50)
(“Shitfaced Shakespeare”,16.50)
(“Aaabeduation – A Magic Show”,17.99)
(“Camille O’Sullivan”,17.99)
(Balletronics,32.00)
(“Edinburgh Royal Tattoo”,32.99)

Select the average price of all shows.

SELECT AVG(price) FROM shows;
avg
15.9569230769230769

Select the price of the least expensive show.

select name, show where price from shows where price IN(selct max price from shows)

select name from shows where price=(select max(price) from shows);
name
Edinburgh Royal Tattoo

Select the sum of the price of all shows.

SELECT SUM(price) FROM shows;
sum
207.44

Select the sum of the price of all shows whose prices is less than £20.

Select the name and price of the most expensive show.

Select the name and price of the second from cheapest show.

Select the names of all users whose names start with the letter “A”.

Select the names of users whose names contain “el”.

JOIN Clauses

The following questions can be answered by using nested SQL statements but ideally you should learn about JOIN clauses to answer them.

  1. Select the time for the Edinburgh Royal Tattoo.
  2. Select the number of users who want to see “Le Haggis”.
  3. Select all of the user names and the count of shows they’re going to see.
  4. SELECT all users who are going to a show at 13:30.

Hints

  • As with anything, if you get stuck, move on, then go back if you have time.
  • Don’t spend all night on it!
  • Use resources online to solve harder ones - there are solutions to these questions that work with what we’ve learnt today, but other tools exist in SQL that could make the queries ‘better’ or ‘easier’.

Notes

Dropping the database

dropdb [name]

loading the file

{{-f [name]}}