SQL DDL & DML

1. SQL DDL & DML Chapters 6 & 7

2. Agenda • History of SQL • DDL • DML • Summary

3. 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, SQL:2011 • Many vendors extend standard

4. 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

5. 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

6. SQL Server tips

7. Sql server sql • SQL Server-specific practices • Use SSMS as UI • Preface each batch of SQL commands with USE dbname • Follow each batch of SQL 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 commands are permanent (ie, "COMMITed")

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

9. Sqlddl

10. 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

11. 1. Create databases • Computer file, physical container for objects • Must USE the Master database • Must have CREATE DATABASE permission • The 'sa' login ID has this permission • CREATE DATABASE dbname [ ON [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] ; • Example: Use Master CREATE DATABASE mis_test;

12. Create database Users & give them access rights • Steps • Create Login • Allows user to connect to the SQL Server instance • Create User • Allows user to access a specific DB in SQL Server • Create Schema (optional) • Specifies logical "container" to store user objects in • Grant Permissions • Gives users specific rights within a DB

13. 2. Create users: logins • Allows users to connect to DB server • Ensure you are USEingthe Master database • Used for authentication • 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; • CREATE LOGIN student1 WITH PASSWORD = 'Password1', DEFAULT_DATABASE = mis_test;

14. 2. Create users: Users • Allows users to access specific database(s) • Ensure you are USEing 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;

15. 3. Access Rights: schemas • Logical grouping (container) of objects w/in a DB • Establishes ownership of objects • Optional step (default is DBO) • Does NOT give owner right to create/access objects!!! • CREATE SCHEMA schema_name [AUTHORIZATION existing_user_name]; • Examples: CREATE SCHEMA student1 AUTHORIZATION student1; CREATE SCHEMA nancy AUTHORIZATION nancy_sanchez; NOTE: would also require an ALTER command to make this schema her default schema

16. 3. 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 CONTROL TO nancy_sanchez; • GRANT CREATE PROCEDURE, CREATE VIEW, CREATE TABLE, CREATE ROLE, CREATE SCHEMA, CREATE SYNONYM, SELECT, INSERT, UPDATE, DELETE, EXECUTE TO student1; • GRANT SELECT ON SCHEMA :: student1 TO PUBLIC; • GRANT INSERT ON northwind.dbo.customer TO student1; *Roles, users, schemas, objects must be created first! Resources must exist first!*

17. 4. Creating structures

18. 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));

19. 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));

20. Example • 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) );

21. 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;

22. 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);

23. 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;

24. Examples • ALTER TABLEOrder_tADD COLUMN OrdType VARCHAR(2); ALTER TABLEOrder_tADD OrdType VARCHAR(2); • ALTER TABLEOrder_tADDCONSTRAINTOrder_Date_Christmas_cc CHECK(DAY(Order_Date) <> 25 and MONTH(Order_Date) <> 12); ALTER TABLE Order_t ADD CONSTRAINT Order_Date_Christmas_cc CHECK (CONVERT(varchar(5),Order_Date,1) <> '12/25'); • 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'); • ALTER TABLEOrder_tDROP COLUMNOrdType; • ALTER TABLEOrder_tDROP CONSTRAINTOrder_Date_Christmas_cc; • ALTER TABLEOrder_tDROP CONSTRAINTOrder_Time_cc; • ALTER USER nancy_sanchezWITH NAME = nsanchez; • ALTER USER nsanchezWITH DEFAULT_SCHEMA = nancy1;

25. 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 SCHEMA :: student1 FROM public;

26. Sqldml

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

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

29. 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...

30. Script Example use mis_test /* I expect to get errors from most of the DROP statements below 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_code numeric(5), 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), 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)); GO

31. 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

32. 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

33. 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); INSERT INTO Order_t (order_id, customer_id) VALUES (9000,8); INSERT INTOOrder_tVALUES (1099, CONVERT (datetime, '03:25:00', 8), 8); 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);

34. 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';

35. 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;

36. Updating data, cont… • Can update a table with values from another table • Example: ALTER TABLE order_t ADD cname 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 cname;

37. Deleting Data • DELETE FROM table_name [WHERE condition(s)]; • Examples: DELETE FROM Order_t WHERECustomer_ID = 2; DELETE FROMOrder_t;