Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL

 SQL

Year 13 Lesson

AllenHeard

May 17, 2017
Tweet

More Decks by AllenHeard

Other Decks in Education

Transcript

  1. 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
  2. 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
  3. 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
  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 : ▪ Date/Time datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
  5. 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).
  6. Using SQL ▪ We will be using a web-based tool

    here http://sqlfiddle.com/ to create and interrogate a database.
  7. 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.
  8. 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.
  9. 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.
  10. 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
  11. 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.
  12. 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
  13. 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
  14. 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
  15. Syntax – SELECT SELECT field_name FROM table_name WHERE condition SELECT

    cusid, cussurname FROM customer WHERE cussurname = 'Roberts' e.g.
  16. Syntax – SELECT ALL FROM A TABLE SELECT * FROM

    table_name SELECT * FROM customer e.g.
  17. Syntax – ORDER BY SELECT * FROM table_name ORDER BY

    field_name ASC|DESC SELECT * FROM customer ORDER BY cussurname ASC e.g.
  18. 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.
  19. 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
  20. 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
  21. Syntax – UPDATE UPDATE table_name SET field_name = new_value WHERE

    field_name = value UPDATE customer SET cussurname = 'Harris' WHERE cusid = 572 e.g.
  22. 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
  23. 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')
  24. TASK – IN ▪ Write an SQL command that outputs

    all details of records if the surname is either Gray or Wonder