SQL

Year 13 Lesson

Transcript

1. SQL Structured Query Language
2. What is SQL? ▪ SQL stands for Structured Query Language ▪ SQL lets you access and manipulate databases ▪ SQL is an ANSI (American National Standards Institute) standard ▪ Useful website for reference http://www.w3schools.com/sql/default.asp
3. What can SQL do? ▪ SQL can execute queries against a database ▪ SQL can retrieve data from a database ▪ SQL can insert records in a database ▪ SQL can update records in a database ▪ SQL can delete records from a database ▪ SQL can create new databases ▪ SQL can create new tables in a database ▪ SQL can create stored procedures in a database ▪ SQL can create views in a database ▪ SQL can set permissions on tables, procedures, and views
4. Data Types ▪ In MySQL there are three main types : text, number, and Date/Time types. ▪ For this course we need to be aware of the following data types : ▪ Text ▪ Number CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters Defined width INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis 4 bytes numeric(p,s) Fixed precision and scale numbers. Allows numbers from - 10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 5-17 bytes
5. Data Types ▪ In MySQL there are three main types : text, number, and Date/Time types. ▪ For this course we need to be aware of the following data types : ▪ Date/Time datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
6. Database Tables ▪ A database most often contains one or more tables. Each table is identified by a name (e.g. 'Customers' or 'Orders'). Tables contain records (rows) with data. ▪ Below is a selection from a 'Customers' table: ▪ The table above contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).
7. Using SQL ▪ We will be using a web-based tool here http://sqlfiddle.com/ to create and interrogate a database.
8. Syntax – CREATE TABLE CREATE TABLE table_name( field_name_1 data_type, field_name_2 data_type ); CREATE TABLE customer ( cusid int, cussurname char(255), datebirth date ); e.g.
9. Syntax – PRIMARY KEY & NOT NULL CREATE TABLE table_name ( field_name_1 data_type PRIMARY KEY NOT NULL, field_name_2 data_type ); CREATE TABLE customer ( cusid int PRIMARY KEY NOT NULL, cussurname char(255) NOT NULL, datebirth date ); e.g.
10. TASK - CREATE TABLE ▪ When using SQL Fiddle to create a database, the commands go in the left side. ▪ When running queries, the command go in the right side. ▪ Keep a copy of your SQL code in another document too.
11. TASK - CREATE TABLE ▪ Create a table called CARS ▪ The table must include fields for make, model, registration and year of registration ▪ Decide on a suitable primary key ▪ Decide on data types and field lengths
12. Syntax – INSERT DATA INSERT INTO table_name(field_name_1, field_name_2) VALUES(value, value); INSERT INTO customer (cusid, cussurname, datebirth) VALUES (001, ‘Williams', '19801130'), (002, ‘Roberts', '19860827'); e.g.
13. TASK – INSERT DATA ▪ INSERT the following data INTO the table CARS Make Model Reg Year Ford Mondeo FJ58 YXR 2008 BMW Mini CX291JB 2012 Citroen Picasso KD96KOP 1996 Rover 216 B420MCM 1990 Toyota Yaris JB03DAB 2012
14. TASK – CREATE TABLE ▪ Create a table called DRIVERS ▪ The table must include fields for Driver Number, First Name, Surname, House Number, Postcode, and Car Registration ▪ Decide on a suitable primary key ▪ Decide on data types and field lengths
15. TASK – INSERT DATA ▪ INSERT the following data INTO the table DRIVERS DriverNum FName Surname House# Postcode Reg 611173 Delyth Roberts 55 LL128PQ FJ58YXR 363731 Donald Gray 219 YO154RW JB03DAB 271964 Stephen Allen 123 HU94EH B420MCM 952746 Alice Wonder 14 L118RF KD96KOP 452849 Harry Shine 67 SR147TS CX291JB
16. Syntax – SELECT SELECT field_name FROM table_name WHERE condition SELECT cusid, cussurname FROM customer WHERE cussurname = 'Roberts' e.g.
17. Syntax – SELECT ALL FROM A TABLE SELECT * FROM table_name SELECT * FROM customer e.g.
18. Syntax – ORDER BY SELECT * FROM table_name ORDER BY field_name ASC|DESC SELECT * FROM customer ORDER BY cussurname ASC e.g.
19. TASK – SELECT ALL FROM A TABLE ▪ Check all the data is in the tables by selecting all from each ▪ Order the Drivers by Surname in ascending order ▪ Order the Cars by year of registration, most recent first.
20. TASK – SELECT ▪ Write an SQL command to output the name and address of the owner of the vehicle B420MCM ▪ Write an SQL command to output all models made by Ford ▪ Write an SQL command to output the name of the driver who lives at L118RF
21. TASK – SELECT - EXTENSION ▪ Write an SQL command to output the registration number of the cars that have the same year of registration as vehicle registration CX291JB
22. Syntax – UPDATE UPDATE table_name SET field_name = new_value WHERE field_name = value UPDATE customer SET cussurname = 'Harris' WHERE cusid = 572 e.g.
23. TASK – UPDATE ▪ Write an SQL command to change the surname of driver 611173 from Roberts to Schaffhausen ▪ Write an SQL command to add the following vehicle to the database: Vauxhall Corsa KV71OMD 2016 ▪ Write an SQL command to change Harry Shine’s vehicle registration to that of the new car above
24. Syntax – IN SELECT fieldname FROM table_name WHERE fieldname IN (value_1, value_2) e.g. ▪ IN keyword allows for multiple search criteria in a WHERE command. SELECT cusid FROM customer WHERE cussurname IN ('Hughes', 'Grant')
25. TASK – IN ▪ Write an SQL command that outputs all details of records if the surname is either Gray or Wonder