SQL - An Introduction - Final (1).pptx

COM106: Introductio n to Databases Introduction to SQL Structured Query Language (SQL) – An Introduction What is SQL? SQL is a Programming Language, designed to manage data in a relational dB Uses simple declarative statements It is a defacto Industry Standard. There is an ANSI sta...

COM106: Introductio n to Databases Introduction to SQL Structured Query Language (SQL) – An Introduction What is SQL? SQL is a Programming Language, designed to manage data in a relational dB Uses simple declarative statements It is a defacto Industry Standard. There is an ANSI standard - although there are many different versions and dialects. SQL Server 2017 uses a version know as Transact-SQL (or T-SQL) T-SQL commands may vary slightly from ANSI-SQL or SQL used in other systems (e.g. MySQL) SQL consists of 3 main components: DDL - Data Definition Language (used in the creation/destruction of relational tables, As used indexes, in the Lab. views etc.) Indentation and layout are not e.g. CREATE TABLE EMP required for execution (empnum smallint not null,They are required for clarity. ename char(15), SQL – An Introduction DML - Data Manipulation Language (used for retrieval, appending, deleting, updating tables, etc) e.g. INSERT INTO EMP DELETE FROM EMP VALUES (1, 'Smith', 10000, 12); WHERE ename = 'Jones'; UPDATE EMP SELECT ename SET salary = 20000 FROM EMP WHERE ename = 'Smith' ; WHERE salary > 20000 ; DCL - Data Control Language (used for creating table permissions, valid users, etc) e.g.GRANT ALL [PRIVILEGES ] ON EMP TO John [WITH GRANT OPTION]; (assuming John is a registered user) Some SQL Reserved Words (See Link in Lab Resources for Complete List) IN SELEC HAVING UPDAT There are also a set of comparison T E operators: FROM GROUP DELET ASC = BY E WHER DISTINC INSER SET Equal to E T T SQL – An Introduction Some Common Data Types Character Strings: char(n) - data entries are consistent in size e.g. char (12) varchar(n) - data entries vary considerably in size e.g. varchar (30) Exact Numerics: tinyint (0 to 255) smallint (-32,768 to 32,767), int (-2,147,483,648 to 2,147,483,648) bigint (-2^63 to 2^63-1) decimal [ (p[ ,s] )] – precision and scale e.g. decimal (7,2) Dates and Times e.g. date = ‘1912-10-25’; (See the T-SQL Language Reference for the Complete List - link in the Lab Resources Folder) SQL – An Introduction Building a Database – Creating the Tables Consider the following relational schema: EMPLOYEE (enum , With sample data as shown ename, salary, floor) PROJECT (pnum , pname, leader) EMPLOYE enum ename salary floor Notice the PKs WORKS_ON (enum* , pnum* , E 852341 Smith 15000 1 and FKs, androle) WORKS_O 852358 Jones 19000 3 how the tables Nenum pnum role 852407 Brown 16000 3 will be linked 852341 121 Manager 852455 White 25100 2 852341 135 Designer 852491 Adams 30500 1 852358 147 Consultant 852514 Doyle 11650 2 Decide on the 852358 135 Consultant 852530 Evans 26980 4 Data Types 852407 216 Assistant 852455 121 Assistant PROJECT 852455 227 Manager pnum pname leader Can an 852491 135 Designer 121 IT Gates attribute be 852491 216 Manager 135 Design Sinclair 852514 121 Assistant NULL? 147 Analysis Einstein 852514 216 Consultant 216 Publicity Saatchi Now, write 852514 251 Manager 227 Theatre Dench the SQL to 852530 147 Manager 251 Sport Shearer create each SQL – An Introduction SQL Style Guide: 1. Create the EMPLOYEE Table Table names and SQL Reserved words – EMPLOYEE (enum , ename, ALL CAPS, words separated by EMPLOYE sunderscore (_) enum ename salary floor E a Attribute names – meaningful, all 852341 Smith 15000 1 llowercase, words separated by 852358 Jones 19000 3 aunderscore (_) 852407 Brown 16000 3 Layoutr – generally, one SQL clause per Don’t waste storage 852455 White 25100 2 yline space – choose data types 852491 Adams 30500 1 , that are just large enough. 852514 Doyle 11650 2 852530 Evans 26980 4 CREATE f TABLE EMPLOYEE ( enuml int not null, ename o char (15), salaryo decimal (7,2), floor rtinyint, Entity Integrity - no CONSTRAINT ) pk_emp PRIMARY attributes participating in the KEY (enum) primary key of a relation can accept null values. ); In this example, pk_emp is the Primary Key Constraint – name given to the Primary Key can be added when the table is Constraint created, or added later using an ALTER TABLE statement. SQL – An Introduction WORKS_O Nenum pnum role 2. Create the WORK_ON Table 852341 121 Manager WORKS_ON (enum* , pnum* , 852341 135 Designer r 852358 147 Consultant CREATE TABLE WORKS_ON ( o 852358 135 Consultant enum int not null, l 852407 216 Assistant pnum smallint not null, e 852455 121 Assistant role char(15), 852455 227 Manager ) CONSTRAINT pk_wo PRIMARY KEY 852491 135 Designer (enum, pnum) We will use an ALTER TABLE 852491 216 Manager 852514 121 Assistant ); statement to make enum and pnum FKs 852514 216 Consultant Data Types must be the same as 852514 251 Manager 3. Create the PROJECT Table PKs in corresponding tables 852530 147 Manager PROJECT (pnum , pname, leader) PROJECT pnum pname leader 121 IT Gates CREATE TABLE PROJECT ( 135 Design Sinclair pnum smallint not null, 147 Analysis Einstein pname char(15), 216 Publicity Saatchi leader char(15), 227 Theatre Dench CONSTRAINT pk_proj PRIMARY 251 Sport Shearer KEY (pnum) SQL – An Introduction Integrity Constraints can be used to apply business rules to the tables. They can be added in the CREATE TABLE statement or later using ALTER TABLE We have already seen NOT NULL and PRIMARY KEY Constraints can be named – as earlier with PRIMARY KEY in the CREATE TABLE statements – or applied directly to a column/attribute – as with not null. Other useful constraints are : DEFAULT − Provides a default value for a column when none is specified. e.g., in the WORKS_ON table role char(15) DEFAULT ‘To be set’, UNIQUE − Ensures that all values in a column are different. e.g., in the PROJECT table leader char(15) UNIQUE, CHECK − Ensures that all the values in a column satisfies certain conditions. SQL – An Introduction Building a Database – Create the Links (FKs) and establish Referential Integrity Relational Schema: EMPLOYEE (enum , ename, salary, floor) PROJECT (pnum , pname, leader) Remember: WORKS_ON If a relational (enum*, pnum*, table includes a role) foreign key (FK) matching the primary key (PK) of another relational table, then every value of the FK must: either be equal to a value of the PK in some tuple (row) or be wholly NULL Foreign Keys can be created, and Referential Integrity established, using an ALTER TABLE statement Requires referencing from the Foreign Key towards a corresponding Primary Key in another table Attributes don’t have to have the same To put referential integrity between enum from WORKS_ON (FK) and name, enum from EMPLOYEE (PK) but MUST have the same Data Type ALTER TABLE WORKS_ON ADD CONSTRAINT fk_wo1 FOREIGN KEY (enum) REFERENCES EMPLOYEE (enum); To put referential integrity between pnum from WORKS_ON (FK) & pnum SQL – An Introduction In general, may not be possible. Other useful ALTER TABLE Commands Depends on data types and Changing a datatype/size (to whole numbers only) if data has been entered already. ALTER TABLE EMPLOYEE ALTER COLUMN salary int; Delete budget attribute from Adding a new attribute (budget to PROJECT) ALTER TABLE PROJECT PROJECT ADD budget decimal (9,2); ALTER TABLE PROJECT DROP COLUMN budget; Adding a PK (if not done in create table) ALTER TABLE EMPLOYEE ADD CONSTRIANT pk_emp PRIMARY KEY (enum); Drop the PK from PROJECT (A two stage process: 1. Drop referential Integrity on PROJECT PK – refer by name 2. Drop the PK from PROJECT – refer by name) ALTER TABLE WORKS_ON (drop referential integrity) DROP CONSTRAINT fk_wo2; ALTER TABLE PROJECT (drop PK) SQL – An Introduction Building a Database – Adding Records General Syntax INSERT INTO TABLENAME (attribute list) VALUES (record values) e.g. Add new employee record 852559 , 'Brown', 18000, 4 INSERT INTO EMPLOYEE (enum, ename, salary, floor) VALUES (852559, 'Brown', 18000, 4) ; Which can be shortened where a whole record is added to: EMPLOYE INSERT INTO EMPLOYEE Eenum ename salary floor VALUES (852559, 'Brown', 18000, 4); 852341 Smith 15000 1 852358 Jones 19000 3 Building a Database – Deleting Records 852407 Brown 16000 3 General Syntax 852455 White 25100 2 852491 Adams 30500 1 DELETE FROM TABLENAME 852514 Doyle 11650 2 WHERE selection clause; 852530 Evans 26980 4 e.g. Delete employees earning above 18000 on the 2nd floor DELETE FROM EMPLOYEE WHERE salary > 18000 AND floor = 2 ; SQL – An Introduction Building a Database – Editing Records General Syntax UPDATE TABLENAME SET attribute = new value WHERE selection clause; e.g. Change the project leader of the Theatre project to Branagh UPDATE PROJECT SET leader = 'Branagh' WHERE pname = ‘theatre'; NOTE – Referential Integrity must be considered when adding, deleting or updating records Using the relational schema and sample data on slide 4, consider each INSERTcorrectly? of the examples above. Will the queries execute INTO EMPLOYEE VALUES (852559, 'Brown', 1. Add new employee record 18000, 4); 852559 , 'Brown', 18000, 4 DELETE FROM EMPLOYEE WHERE salary > 18000 2. Delete employees earning AND floor = 2 ; above 18000 on the 2nd floor UPDATE PROJECT 3. Change the project leader SET leader = 'Branagh‘ of the Theatre project to Branagh SQL – An Introduction Consider the following relational schema: EMPLOYEE (enum , ename, salary, floor) PROJECT (pnum , pname, leader) With sample data as shown WORKS_ON (enum* , pnum* , role) Will the following queriesEMPLOYEE execute correctly? WORKS_ON INSERT INTO EMPLOYEE  VALUES (852559, 'Brown', enum enam salar floo e y r enum pnum 852341 121 role Manager 18000, 4); 852341 Smith 15000 1 852341 135 Designer 852358 Jones 19000 3 852358 147 Consultant 852407 Brown 16000 3 852358 135 Consultant DELETE FROM EMPLOYEE WHERE salary > 18000  852455 White 25100 2 852491 Adams 30500 1 852407 216 852455 121 Assistant Assistant AND floor = 2 ; 852514 Doyle 11650 2 852455 227 Manager 852530 Evans 26980 4 852491 135 Designer PROJECT pnum pname leader 852491 216 Manager 121 IT Gates 852514 121 Assistant UPDATE PROJECT SET leader =  135 Design Sinclair 147 Analysis Einstein 852514 216 852514 251 Consultant Manager 'Branagh‘ 216 Publicity Saatchi 852530 147 Manager WHERE pname = 227 Theatre Dench ‘Theatre'; 251 Sport Shearer