Data Modeling and Databases Lab 3: Introduction to SQL

1. Data Modeling and Databases Lab 3: Introduction to SQL

Bulat Gabbasov, Albina Sayfeeva
Innopolis University
2016

2. Basic SQL query structure

• Basic SQL query structure consists of SELECT, FROM, WHERE, GROUP BY and ORDER BY clauses.
• SELECT [ALL | DISTINCT] expressions
• specifies the columns to appear in the result
• distinct keyword can be used to eliminate duplicates
• FROM from_items
• specifies the relations to be used
• WHERE condition
• filters the tuples
• GROUP BY expression
• groups rows with the same column values
• the HAVING construct can be used to further filter the groups
• ORDER BY expression
• defines the order of the resulting tuples
9/10/16
Data Modeling and Databases
2

3. Data manipulation

• INSERT
• Inserts a tuple into the specified table
• INSERT INTO tablename (list of columns)
VALUES (list of values), ...
• UPDATE
• Updates all tuples that match specified condition
• UPDATE tablename SET column = newvalue, ...
WHERE condition
• DELETE
• Deletes all tuples that match specified condition
• DELETE FROM tablename WHERE condition
9/10/16
Data Modeling and Databases
3

4. Inserting

• Create a new student Harvey Specter:
INSERT INTO students
(student_id, firstname, lastname) VALUES
(1, 'Harvey', 'Specter')
9/10/16
Data Modeling and Databases
4

5. Updating

Change firstname of all students having student_id = 1 to ‘John’:
UPDATE students
SET firstname = 'John'
WHERE student_id = 1
9/10/16
Data Modeling and Databases
5

6. Deleting

• Delete student having student_id = 1 from table students:
DELETE FROM students WHERE student_id = 1
9/10/16
Data Modeling and Databases
6

7. Expressions

• Calculate expression 1 + 1 and name it as two:
SELECT 1 + 1 AS two
9/10/16
Data Modeling and Databases
7

8. Tables

• Return list of all students:
SELECT * FROM students
9/10/16
Data Modeling and Databases
8

9. Exercise

• Insert a new department named ‘Machine Learning’ and leaded with professor identified by professor_id = 1
INSERT INTO departments
VALUES (4, 'Machine Learning', 1)
• Change name of the newly created department to ’Advanced Machine Learning’
UPDATE departments
SET name = 'Advanced Machine Learning'
WHERE name = 'Machine Learning’
• Delete new newly created department
DELETE FROM departments
WHERE name = 'Advanced Machine Learning'
9/10/16
Data Modeling and Databases
9

10. Exercise

• Find the address of the student with first name "Donna”
SELECT address FROM students
WHERE firstname = 'Donna'
9/10/16
Data Modeling and Databases
10

11. Exercise

• Find all students who are either male or are from Kazan
SELECT * FROM students
WHERE gender = 'm' or address = 'Kazan'
9/10/16
Data Modeling and Databases
11

12. Exercise

• Find all courses that worth at least 9 credits and are given by MSIT
department
• Hint: department_id for MSIT-SE is 1.
SELECT * FROM courses
WHERE credits >= 9 AND department_id = 1
9/10/16
Data Modeling and Databases
12

13. Exercise

• Find names and salaries of professors who earn less than 15 000
SELECT firstname, lastname, salary
FROM professors WHERE salary < 15000
9/10/16
Data Modeling and Databases
13

14. Exercise

• Find students born earlier than 1980
SELECT * FROM students
WHERE birthdate < '1980-01-01'
9/10/16
Data Modeling and Databases
14

15. Exercise

• List full names of all students living in Moscow
• Hint: concatenation operator a || b
SELECT
firstname || ‘ ‘ || lastname AS fullname
, address
FROM Students WHERE address = ‘Moscow’
9/10/16
Data Modeling and Databases
15

16. Exercise

• Find students who's address contains "k" letter
SELECT * FROM students WHERE address LIKE '%k%'
9/10/16
Data Modeling and Databases
16

17. Exercise

• Find students who's lastname consists of 7 letters and ends with "n”
SELECT * FROM students
WHERE lastname LIKE '______n'
9/10/16
Data Modeling and Databases
17

18. Exercise

• Order and display students by lastname (alphabetically)
SELECT * FROM students
ORDER BY lastname
9/10/16
Data Modeling and Databases
18

19. Exercise

• Order and display students by lastname and then by firstname
(alphabetically)
SELECT * FROM students
ORDER BY lastname, firstname
9/10/16
Data Modeling and Databases
19

20. Exercise

• Order by login : first letter of firstname + full lastname in descending order
• Hint: use SUBSTRING(column from begin for length)
SELECT SUBSTRING(firstname from 1 for 1)
|| lastname AS login, *
FROM students
ORDER BY 1 DESC
9/10/16
Data Modeling and Databases
20

21. Exercise

• Find names of male students who got more than 50 for any course
SELECT s.firstname, s.lastname
FROM students s
WHERE gender = 'm' AND
EXISTS(SELECT 1 FROM enrollment e
WHERE e.student_id = s.student_id
AND e.grade > 50)
9/10/16
Data Modeling and Databases
21

22. Exercise

• Which students are enrolled in DMD course?
SELECT s.* FROM students s
NATURAL JOIN enrollment e
NATURAL JOIN courses c
WHERE c.name = 'DMD'
9/10/16
Data Modeling and Databases
22

23. QA?

9/10/16
Data Modeling and Databases
23