1 / 111

SQL

SQL. Chapters 6 & 7. Agenda. MS SQL Server Background Architecture Access SQL Background DDL DML Queries. Microsoft SQL Server overview. What is sql server?. Database Management System RDBMS XML *OLAP Services Query Full Text Search *BI Integration Analysis Reporting

amity-england
Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Chapters 6 & 7

  2. Agenda • MS SQL Server • Background • Architecture • Access • SQL • Background • DDL • DML • Queries

  3. Microsoft SQL Server overview

  4. What is sql server? • Database Management System • RDBMS • XML • *OLAP • Services • Query • Full Text Search • *BI • Integration • Analysis • Reporting • Development Tools • SQLCMD • SQL Server Management Studio (SSMS) • *SQL Server Data Tools • Visual Studio

  5. Sql server Instance Architecture CLIENT SERVER Memory Pool System info Data pages Log info Exec Plan Info Relational Engine User/ Client Open Data Services Client/Server Net Libraries Connection info OLE DB Storage Engine OLE DB for SQLServer OLE DB for Oracle Local SQL Server Database System Databases User Databases msdb mis4340 master sales Remote SQLServer Oracle Server temp model alumni

  6. Accessing sql server • Installing/Accessing SQL Server document • To install your own copy of SSMS (client) and SQL Server Express Edition (database server) • Install SQL Server 2012 Express Edition with Tools • Log in to SQL Server Management Studio • Create a New Database in SQL Server Express Edition • To access SQL Server from lab/classroom computers • Log In to SQL Server Management Studio • Server Name: HSB-MIS-SQL

  7. SQL background Chapters 6 & 7

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

  9. 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 • Internet Resources • http://www.functionx.com/sqlserver/ • http://www.w3schools.com/sql/default.asp • Sams Teach Yourself SQL in 10 Minutes, Forta, B. 2012. ISBN 97806 72336072

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

  11. SQL Server tips

  12. Sql server sql • SQL Server-specific practices • Use SSMS as UI • Preface (each batch of) SQL commands with USE dbname • Follow SQL DDL command(s) with GO • GO Must be on line by itself, 1st column • 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")

  13. Sql server data type conversions • Convert data from one data type to another • Suggest CONVERT for date and/or time formatting, manipulation • Suggest CAST for all other data type conversions • CONVERT ( target_data_type [ ( length ) ] , value [ , stylecode ] ) • CAST ( value AS target_data_type[ ( length ) ] ) • Examples: SELECT 'List price: ' + CAST(standard_price AS varchar) FROM product_t; -- OR -- SELECT 'List price: ' + CONVERT(varchar, standard_price) FROM product_t; Print 'old zip: ' + CAST(@czip AS varchar)…; -- OR -- Print 'old zip: ' + CONVERT(varchar, @czip)…; ALTER TABLE Order_t ADD CONSTRAINT MyCC CHECK (CONVERT(varchar(2),Order_Date,8) >= '06' AND CONVERT (varchar(2), order_date, 8) <='18');

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

  15. Sqlddl

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

  17. 1. Creating 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: • CREATE DATABASE mis_test;

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

  19. 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]; • Example: • CREATE LOGIN [baylor\nancy_sanchez] FROM WINDOWS WITH DEFAULT_DATABASE = mis_test; • CREATE LOGIN student1 WITH PASSWORD = 'Password1', DEFAULT_DATABASE = mis_test;

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

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

  22. 3. Access rights: GrantS* • Give specific rights to access DB resources • Server • Database • Schema • Object/Statement • 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!*

  23. 4. Creating structures

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

  25. Establishing Integrity Controls CREATE TABLEtable_name (column_namedata_type [(length)] [NULL|NOT NULL| DEFAULT value], other columns…, CONSTRAINTconstraint_namePRIMARY KEY (column_name [, column_name, …]), CONSTRAINTconstraint_nameFOREIGN KEY (column_name [, column_name, …]) REFERENCES table_name (column_name) [ON DELETE|UPDATE NO ACTION|CASCADE | SET NULL], CONSTRAINTconstraint_nameCHECK (condition));

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

  27. Example: Create table from another table • Create a new table with the same structure as the Order_t table. SELECT TOP 0 * INTO Order_Copy FROM Order_t; • Create a new table with the same structure as the Order_t table, AND load it with rows for customer #1. SELECT * INTO Order_Customer1 FROM Order_t WHERE customer_ID = 1;

  28. 4. Creating (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); • What fields make good secondary index keys?

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

  30. Examples • ALTER TABLEOrder_tADD COLUMN OrdType VARCHAR(2); ALTER TABLEOrder_tADD OrdType VARCHAR(2); ALTER TABLE PLAYERS_COLLEGES ALTER COLUMN NAME VARCHAR(100) NOT NULL; • ALTER TABLEOrder_tADD OrdType VARCHAR(2) CONSTRAINT OrdTypeChkCHECK (OrdTypeIN ('complete', 'inprocess', 'cancelled')); • 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;

  31. 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 [ON object] FROM username; • REVOKE SELECT ONnorthwind.dbo.customer FROM student1; • REVOKE SELECT ON SCHEMA :: student1 FROM public; • In what order would you drop the 4 Orders tables?

  32. Sqldml

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

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

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

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

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

  38. 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: • Tools | Options | Query Execution | SQL Server | Ansi • Tools | Options | Query Execution | SQL Server | Advanced

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

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

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

  42. 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; GO ALTER TABLE order_t DROP COLUMN cname;

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

  44. Sql queries

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

  46. Retrieving data

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

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

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

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

More Related