SQL

1. SQL

2. SQL DDL & DML

3. DDL/DML • SQL Overview • DDL • DML • Summary

4. History of SQL • Structured Query Language (SQL) • conceptualized by Codd, early '70s • prototyped by IBM, mid-'70s as SEQUEL • now standard for creating/manipulating relational structures • SQL-86, SQL-89, SQL-92 (+ SQL/CLI, '95), SQL-99 (SQL3), SQL:2003, SQL:2008 • Many vendors extend standard

5. SQL Commands • Data Definition Language (DDL) • define/change/remove database objects • Access to objects • Data Manipulation Language (DML) • maintain and query a data • control transaction integrity • Resources • http://www.w3schools.com/sql/default.asp • http://www.functionx.com/sqlserver/ • Sams Teach Yourself SQL in 10 Minutes, Forta, B. 2012. ISBN 9780672336072

6. SQL Commands, con't… • Common Practices: • UPPERCASE text for reserved words • ProperCase for your names • Semi-colon to signify end of statement • Indentions and extra parentheses for readability • Use meaningful names

7. Ms SQL Server tips

8. MS Sql server-specific practices • Use SQL Server Management Studio as UI • Preface each batch of SQL commands with “USE DatabaseName” • Follow each batch of SQL DDL commands with “GO” • Must be on line by itself • Use double quotes for object names, single quotes for literal strings • Click !Execute button (OR F5) to execute SQL commands • Commands will run without semi-colon, BUT use semi-colons anyway • All DDL and DML (Insert, Update, Delete) commands are permanent (ie, "COMMITed")

9. MS Sql server Object References • Fully-Qualified Names • Server.Database.Schema.Object • Server/Instance Name • Defaults to local server OR the server you're connected to • Created when SQL Server Express Edition installed • Database Name • Defaults to “Master” OR the database you're connected to • Owner/Schema Name • Defaults to “DBO” or the user's default schema • Object/Table Name • Required! • Examples: • [Gina-Green\SQLExpress].cit215db.student1.registration -OR- • registration

10. Sqlddl

11. DDL • Creating Objects • Databases • Create database • Database Users • Create login • Create user • Database access rights • Create schema • Grant (permissions) • Data structures • Create table • Create (secondary) indexes • Maintaining Objects, Access • Alter • Drop • Revoke

12. 1. Create databases • Computer file, physical container for objects • Must USE the Master database • Must have CREATE DATABASE permission • CREATE DATABASE dbname [ ON [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] ; • Example: • CREATE DATABASE mis_test;

13. 2. Create database Users • Steps • Create Login • Allows user to connect to the SQL Server instance • Create User • Allows user to access a specific DB in SQL Server

14. 2a. Create users: logins • Allows users to connect to DB server • Must USE the Master database • Format based on authentication method: • Windows • CREATE LOGIN existing_windows_login FROM WINDOWS [WITH DEFAULT_DATABASE = dbname]; • SQL Server • CREATE LOGIN new_db_login WITH PASSWORD = 'complex_pw' [, DEFAULT_DATABASE = dbname]; • Examples: • CREATE LOGIN [baylor\nancy_sanchez] FROM WINDOWS WITH DEFAULT_DATABASE = mis_test; - OR - • CREATE LOGIN nancy_sanchez WITH PASSWORD = 'Password1', DEFAULT_DATABASE = mis_test;

15. 2b. Create users: Users • Allows users to access specific database(s) • Must USE a specific database first • CREATE USER user_name [FOR LOGIN existing_db_login] | [WITHOUT LOGIN] [WITH DEFAULT_SCHEMA = schema_name]; • Example: • CREATE USER nancy_sanchez FOR LOGIN [baylor\nancy_sanchez]; • CREATE USER student1 FOR LOGIN student1 WITH DEFAULT_SCHEMA = student1;

16. 3. give database access rights • Steps • Create Schema (optional) • Establishes ownership within a database • Grant Permissions • Allows user to access objects and perform activities within a database

17. 3a. Create schemas • Establishes ownership of objects • Logical grouping (container) of objects w/in a DB • Optional step (default is DBO) • Does NOT give owner right to create/access objects!!! • CREATE SCHEMA schema_name [AUTHORIZATION existing_user_name]; • Example: CREATE SCHEMA nancy AUTHORIZATION nancy_sanchez;

18. 3b. Access Rights: Grants* • Give specific rights to access DB resources • Server • Database • Schema • Object • GRANT privilege [, privilege, role, …] [ON object_name] TO existing_user_name [WITH GRANT OPTION]; • Examples: • GRANT CREATE PROCEDURE, CREATE VIEW, CREATE TABLE, CREATE ROLE, CREATE SCHEMA, CREATE SYNONYM, SELECT, INSERT, UPDATE, DELETE, EXECUTE TO student1; • GRANT SELECT ON student TO PUBLIC; • GRANT INSERT ON northwind.dbo.customer TO student1; *Roles, users, schemas, objects must be created first!

19. 4. Creating structures

20. 4. Create tables • CREATE TABLEtable_name (column1_name data_type [(length)], column2_name data_type [(length)], …); • Example: CREATE TABLEOrder_t (Order_ID NUMERIC (4), Order_Date DATETIME, Customer_ID NUMERIC (4,0));

21. Establishing Integrity Controls CREATE TABLE table_name (column_name data_type [(length)] [NULL|NOT NULL| DEFAULT value], other columns…, CONSTRAINT constraint_name PRIMARY KEY (column_name [, column_name, …]), CONSTRAINT constraint_name FOREIGN KEY (column_name [, column_name, …]) REFERENCES table_name (column_name) [ON DELETE|UPDATE NO ACTION|CASCADE], CONSTRAINT constraint_name CHECK (condition));

22. Example: create table with integrity controls • CREATE TABLE Order_t ( Order_ID NUMERIC(4) NOT NULL, Order_Date DATETIME DEFAULT GETDATE(), Customer_ID NUMERIC(4,0), CONSTRAINTOrder_pkPRIMARY KEY (Order_ID), CONSTRAINTOrder_customer_id_fkFOREIGN KEY (Customer_ID) REFERENCESCustomer_t (Customer_ID) ON DELETE CASCADE, CONSTRAINTOrder_Date_ccCHECK (YEAR(Order_Date) >= 2000) );

23. Example: Create table from another table • Create a new table with the same structure as the Order_t table. SELECTTOP 0 * INTOOrder_Copy FROMOrder_t; • Create a new table with the same structure as the Order_t table, and load it with data for customer #1. SELECT * INTO Order_Customer1 FROM Order_t WHEREcustomer_ID = 1;

24. 4. Create (secondary) Indexes • CREATE INDEX index_nameONtable_name (column_name[, column_name,…]); • Examples: CREATE INDEX Order_Cust_idxONOrder_t (Customer_ID); CREATE INDEXCustID_OrdIDONOrder_t (Customer_ID, Order_ID);

25. Changing object definitions • ALTER LOGIN login ENABLE|DISABLE; DEFAULT_DATABASE =dbname; • ALTER USER existing_user_name WITH NAME = new_user_name; WITH DEFAULT_SCHEMA = schema_name; • ALTER SCHEMAschema_name TRANSFER schema.table_name; • ALTER AUTHORIZATION ON SCHEMA :: schema_nameTO user_name; • ALTER TABLEtable_name ADD column_namedata_type [(length)]; ADDCONSTRAINTconstraint_information; ALTER COLUMN column_namenew_data_type [new_length]; DROP COLUMNcolumn_name; DROP CONSTRAINTconstraint_name;

26. Examples • Syntax for changing a column definition • ALTER TABLE PLAYERS_COLLEGES ALTER COLUMN NAME VARCHAR(100) NOT NULL; • Syntax for adding a column • ALTER TABLEOrder_tADD COLUMN OrdType VARCHAR(2); ALTER TABLEOrder_tADD OrdType VARCHAR(2); • Ensure no orders placed on Christmas day • ALTER TABLEOrder_tADDCONSTRAINTOrder_Date_Christmas_cc CHECK(DAY(Order_Date) <> 25 and MONTH(Order_Date) <> 12); ALTER TABLE Order_tADD CONSTRAINT Order_Date_Christmas_cc CHECK (CONVERT(varchar(5),Order_Date,1) <> '12/25'); • Add a constraint where current rows may violate the constraint • ALTER TABLEOrder_tADDCONSTRAINTOrder_Time_cc CHECK (CONVERT(varchar(2),order_date,8)>='06' AND CONVERT(varchar(2),order_date,8)<='18'); ALTER TABLEOrder_tWITH NOCHECK ADDCONSTRAINTOrder_Time_cc CHECK (CONVERT(varchar(2),order_date,8)>='06' AND CONVERT(varchar(2),order_date,8)<='18'); • Remove columns, constraints • ALTER TABLEOrder_tDROP COLUMNOrdType; • ALTER TABLEOrder_tDROP CONSTRAINTOrder_Date_Christmas_cc; • ALTER TABLEOrder_tDROP CONSTRAINTOrder_Time_cc; • Change a user name • ALTER USER nancy_sanchezWITH NAME = nsanchez; • Assign a default schema to an existing user • ALTER USER nsanchezWITH DEFAULT_SCHEMA = nancy1;

27. Deleting objects or object access • DROP object_typeobject_name; • DROP INDEX order_cust_idx; • DROP TABLE order_t; • DROP SCHEMA nancy; • DROP DATABASEmis_test; • DROP USER nsanchez; • DROP LOGIN [baylor\nancy_sanchez]; • REVOKE privilege FROM user_name; • REVOKE SELECT ONnorthwind.dbo.customerFROM student1; • REVOKE SELECT ON student FROM public;

28. Sqldml

29. DML • Batching SQL Commands • Inserting Data • Updating Data • Deleting Data • Querying Data • Single Table • Multiple Tables

30. Script Files • File of one or more SQL (T-SQL) commands • Good for batching multiple commands, automating tasks, and code sharing/reuse SSMS Database Script File SQL... SQL… SQL...

31. use mis_test /* I frequently begin scripts that include CREATE statements with commands that DROP the objects first */ /* I expect to get errors from most of the DROP statements on the first time the script is run since the tables don’t exist yet */ drop table order_line_t; drop table product_t; drop table order_t; drop table customer_t; GO /* create customer table */ create table customer_t (customer_id numeric(11,0) not null, customer_namevarchar(25) not null, customer_addressvarchar(30), city varchar(20), state varchar(2) default 'tx', postal_codevarchar(12), constraint customer_pk primary key (customer_id)); GO /* add data to customer table */ insert into customer_t values (001, 'contemporary casuals','1355 s. hines blvd.','gainesville','fl','32601'); insert into customer_t values (002, 'value furniture','15145 sw 17th st.','plano','tx','75094'); insert into customer_t values (003, 'home furnishings','1900 allard ave.','albany','ny','12209'); insert into customer_t values (004, 'eastern furniture','1925 beltline rd.','carteret','nj','07008'); insert into customer_t values (005, 'impressions','5585 westcott ct.','sacramento','ca','94206'); insert into customer_t values (006, 'furniture gallery','325 flatiron dr.','boulder','co','80514'); insert into customer_t values (007, 'period furnishings','394 rainbow dr.','seattle','wa','97954'); insert into customer_t values (008, 'california classics','816 peach rd.','santa clara','ca','96915'); insert into customer_t values (009, 'm and h casual furniture','3709 first st.','clearwater','fl','34620'); insert into customer_t values (010, 'seminole interiors','2400 rocky point dr.','seminole','fl','34646'); insert into customer_t values (011, 'american euro lifestyles','2424 missouriave. n.','prospect park','nj','07508'); insert into customer_t values (012, 'battle creek furniture','345 capitol ave. sw.','battle creek','mi','49015'); insert into customer_t values (013, 'heritage furnishings','66789 college ave.','carlisle','pa','17013'); insert into customer_t values (014, 'kaneohe homes','112 kiowai st.','kaneohe','hi','96744'); insert into customer_t values (015, 'mountain scenes','4132 main street','ogden','ut','84403'); GO CREATE TABLE Order_t (Order_ID NUMERIC(4) NOT NULL, Order_Date DATETIME DEFAULT GETDATE(), Customer_ID NUMERIC(4,0), CONSTRAINT Order_pk PRIMARY KEY (Order_ID), CONSTRAINT Order_customer_id_fk FOREIGN KEY (Customer_ID) REFERENCES Customer_t (Customer_ID) ON DELETE CASCADE, CONSTRAINT Order_Date_cc CHECK (YEAR(Order_Date) >= 2000)); GO /* insert values into the order table */ insert into order_t values (1001, '21-OCT-00',1); insert into order_t values (1002, '21-OCT-00',8); insert into order_t values (1003, '22-OCT-00',15); insert into order_t values (1004, '22-OCT-00',5); insert into order_t values (1005, '24-OCT-00',3); insert into order_t values (1006, '27-OCT-00',2); insert into order_t values (1007, '27-OCT-00',11); insert into order_t values (1008, '30-OCT-00',12); insert into order_t values (1009, '05-NOV-00',4); insert into order_t values (1010, '05-NOV-00',1); Script Example

32. Steps to Create/Run Scripts • Create and edit script using text editor • SSMS Query Editor • Other text editor • Eg, Notepad or Notepad++ or Editpad, … • Save script as a file with .sql extension • Run script from SSMS • File | Open | File… • Click !Execute button (OR F5) • executes all commands in script

33. Transaction Control • What is a Transaction? • Properties: ACID • Transaction Control • Commit; • eg, save • Rollback; • eg, undo • Default in SSMS is to: • Autocommit • Read committed data • To change SSMS defaults (don't do!): • Tools | Options | Query Execution | SQL Server | Ansi • Tools | Options | Query Execution | SQL Server | Advanced

34. Inserting Data • INSERT INTO table_name [(column1_name, column2_name, …)] VALUES (column1_value, column2_value, …); • Examples (first create the order tables in figure 7-3): INSERT INTO Order_tVALUES (1001, '21-OCT-00',1);  PK already exists INSERT INTO Order_t (order_id, customer_id) VALUES (9000,8); INSERT INTOOrder_tVALUES  DATETIME requires (1099, CONVERT (datetime, '03:25:00', 8), 8); both date AND time INSERT INTOOrder_tVALUES (1099, CONVERT (varchar(10), getdate(), 126) + 'T03:25:00', 8); INSERT INTOOrder_tVALUES (1098, CONVERT(varchar(4),'2003',112), 2); INSERT INTOOrder_tVALUES (1097, CONVERT (varchar(17), '02 MAY 2009 15:30', 113), 2);

35. Inserting Data, cont… • Can insert multiple rows in a single INSERT • E.G., INSERT INTO tbl1 (fname, lname) VALUES ('Tom', 'Smith'), ('Geri', 'Green'), ('Leo', 'Smith'); INSERT INTOSeniorStudents SELECT * FROM student WHERE classification = 'sr';

36. Updating Data • UPDATE table_name SET column1_name = new_value [,column2_name = new value,…] [WHERE condition(s)]; • Examples: UPDATE Order_t SET Customer_ID = 2 WHERECustomer_ID = 1; UPDATEOrder_t SET Order_Date = CONVERT (varchar(10), Order_Date ,120) + ' ' + '15:15:00' WHEREOrder_ID = 1010;

37. Updating data, cont… • Can update a table with values from another table • Example: ALTER TABLE order_t ADD CustomerName VARCHAR(50); UPDATE order_t SET cname = customer_t.customer_name FROM order_t, customer_t WHERE order_t.customer_id = customer_t.customer_id; ALTER TABLE order_t DROP COLUMN CustomerName; For this example, temporarily add a CustomerName column to the order_t table End of example so remove the temporary CustomerName column in the order_t table

38. Deleting Data • DELETE FROM table_name [WHERE condition(s)]; • Examples: DELETE FROM Order_t WHERECustomer_ID = 2; DELETE FROMOrder_t;  removes all rows

39. Sql queries

40. Querying Data • Retrieving Data • Sorting Data • Filtering Retrieved Data • Combining filters • Wildcard filters • Creating Calculated Fields • Using Functions • Grouping Data • Filtering groups • Accessing Data from Multiple Tables • Subqueries • Joins • Creating Data Views

41. Retrieving data

42. Querying Tables • Basic Format: SELECT column(s) FROM table [WHERE condition(s)]; • Rules: • SELECT must be first clause • FROM must be second clause • Case does not matter (in SQL Server) • Table/column names must be spelled as in the database • Use double quotes for object names, single quotes for literal strings

43. Selecting all rows, specific columns • SELECT column1[, column2, column3,…,columnX] FROM table; • List all customer IDs on orders SELECTcustomer_ID FROMorder_t;

44. Selecting All Rows, all columns • SELECT * FROM table; • Display information about all orders SELECT * FROM order_t;

45. Selecting Unique Rows • SELECT DISTINCT column(s) FROM table; • List all customers who've placed orders. SELECTDISTINCTCustomer_IDFROMOrder_t;

46. Sorting data

47. Sorting Results • ORDER BY • Orders results in ASC or DESC order of column(s) • List customer names and addresses in descending order of customer's name. SELECT Customer_name, Customer_address FROMCustomer_t ORDER BYCustomer_nameDESC; • List product numbers, descriptions, and quantities on hand for all products. Show products with highest quantities first, and then in alphabetical order of product description. SELECTProduct_id, product_description, qty_on_hand FROMProduct_t ORDER BYqty_on_hand DESC, product_description ASC;

48. filtering data

49. Search Conditions • For retrieving specific rows: • Comparison Operators • Boolean Operators • Special Operators • Calculated Fields (Expressions) • SELECT column(s) FROM table WHERE <search condition(s)>;

50. Comparison Operators, con't... • Comparison Operators: = equal to > greater than < less than >= greater than or equal to <= less than or equal to <> not equal to