Fundamentals of Databases SQL Statements Computer Science SQL

Fundamentals of Databases SQL Statements Computer Science

SQL Statements • SQL stands for Structured Query Language – A scripting language lets us access and manipulate databases • With it, we can: – Execute queries against a database – Retrieve data from a database – Insert/update/delete records – Create new databases/tables – Set permissions on tables/databases

SQL Statements • We often use SQL when working with a RDBMS – Relational Database Management System • It’s a piece of software that helps manage a relational database • SQL is split into two categories – Data Definition Language (DDL): Creates, alters, and drops data – Data Manipulation Language (DML): Inserts, updates, deletes, and selects data

SQL Statements: CREATE • Let’s look over a whole bunch of statements we can make – Starting with CREATE – Let’s us create databases/tables

SQL Statements: Datatypes • When making fields, we have to use certain datatypes • There’s a lot more of them than compared to a programming language (C#, Java) – INTEGER: Whole number – VARCHAR: ASCII text – NVARCHAR: Unicode text – DATETIME: Dates – BIT: A single bit (0/1) for true or false – MONEY: For currency – FLOAT/REAL: For decimal values

• Make SQL statements that create the following tables: Donor - ID - Name - Address - Donation. Type Donation - ID - Amount - Date. Given - Donor. ID

SQL Statements: INSERT • We can INSERT to add a record to a table – Needs data for any NOT NULL field

• Insert the following records into the Donor table: Donor. Id Name Address Donation. Type 1 Adam 1 Granby Court, Weekly Reading 2 Jones 20, Kennet Island, Surrey Monthly 3 Sarah 10 Lonodn Road, London Monthly 4 George 15 New Road, Maidenhead Weekly

• Insert the following records into the Donation table Donation. Id Amount. Given Date. Donation. Given Donor. Id 1 5 01/01/2016 1 2 15 10/01/2016 2 3 25 05/02/2016 3 4 10 12/02/2016 1 5 15 20/02/2016 4 6 5 03/03/2016 3

SQL Statements: UPDATE • Lets us change a record’s values – Can change all records in a single table – Can also change selective records in a table using WHERE

• Donor George wants to change his donation type from “Weekly” to “Monthly” • Write an UPDATE statement to perform this change

SQL Statements: DELETE • Removes records from a table – Will remove all records from a table unless WHERE is used

• Remove the following from the Donation table – The record with a Donor. ID of 3 – The record with a Donation. Date of 03/03/2016

SQL Statements: SELECT • Extracts data from one or more tables – Can specify which fields to get – Will get all records unless we specify using WHERE – Can also get the records in a certain order using ORDER BY

SQL Statements: WHERE Conditions • WHERE works off of logical conditions – And only performs the statement it’s attached to when this whole expression returns true • Can use the following operations – Relational Operators: = < > != <= >= – Logical Operators: AND OR NOT – Pattern Matching: LIKE – List Values: IN – Range of Values: BETWEEN

SQL Statements: Joins • Sometimes, when extracting data, we may need to retrieve data from multiple tables – Especially when they are linked in some way • There are quite a few ways of joining two tables – Inner Join: Get records from both tables if conditions are met in both tables – Left/Right Join: Get records from both tables that match all conditions, and also get all records from left/right table – Outer Join: Get all records from both tables

• Create a Department table (and insert the given records) Dept_Id 1 Dept_Name Admin 2 Teaching 3 Development 4 HR

• Create an Employee table (and insert the given records) Emp_Id Emp_Name Address City Join_Date Salary Dept_Id 1 Maria Anders 17 Willesden Green London 01/02/1997 15000 1 2 Cristina Berglund 18 Cumberland Road Reading 01/04/1997 17000 1 3 Francisco Chang Maidenhead 10/04/1997 13000 2 4 Roland Mendel 20 London Road Reading 05/08/1997 18000 3 5 Diego Roel 12 Southall London 02/01/1997 20000 3 6 Eduardo Saavedra 3 High Street Maidenhead 10/11/1997 17500 2 7 Helen Bennett 10 Garden House, Twyford 01/03/1998 22000 3 8 Philip Cramer 90 Oxford Road High Wycombe 04/06/1998 21500 1 9 Yoshi Tannamuri Reading 05/11/1998 13000 2 10 Giovanni Rovelli Basingstoke 10/11/1998 16550 2 10 High Street

• Display all the details of all the employees from the above table. • Display employees’ name, date of joining and salary. • Display the employee details who joined the company on ‘ 10/11/1998’. • Display the employee details where name start from letter ‘H’. • Display employee name and salary that has salary above 15000. • Display the employee details who joined in the November month of 1998. • Display those records which have Salary greater than 18000 but less than 24000. • Display all the employee names, salary and their department name. • Display the employee details for department 3 with salary more than 20000. • Display all the employee details except ‘Teachers’. • Display the rows where the address is not empty.