slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL PowerPoint Presentation
Download Presentation
SQL

Loading in 2 Seconds...

play fullscreen
1 / 105

SQL - PowerPoint PPT Presentation


  • 166 Views
  • Uploaded on

SQL. SQL DDL & DML. DDL/DML. SQL Overview DDL DML Summary. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL' - branden-hood


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
ddl dml
DDL/DML
  • SQL Overview
  • DDL
  • DML
  • Summary
history of sql
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
sql commands
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
sql commands con t
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
ms sql server specific practices
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")
ms sql server object references
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
slide11
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
1 create databases
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;
2 create database users
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
2a create users logins
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;

2b create users users
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;

3 give database access rights
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
3a create schemas
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;

3b access rights grants
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!

4 create tables
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));

establishing integrity controls
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));

example create table with integrity controls
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)

);

example create table from another table
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;

4 create secondary indexes
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);

changing object definitions
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;

examples
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;
deleting objects or object access
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;
slide29
DML
  • Batching SQL Commands
  • Inserting Data
  • Updating Data
  • Deleting Data
  • Querying Data
    • Single Table
    • Multiple Tables
script files
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...

script example

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
steps to create run scripts
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
transaction control
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
inserting data
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);

inserting data cont
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';

updating data
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;

updating data cont
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

deleting data
Deleting Data
  • DELETE FROM table_name

[WHERE condition(s)];

  • Examples:

DELETE FROM Order_t

WHERECustomer_ID = 2;

DELETE FROMOrder_t;  removes all rows

querying data
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
querying tables
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
selecting all rows specific columns
Selecting all rows, specific columns
  • SELECT column1[, column2, column3,…,columnX]

FROM table;

  • List all customer IDs on orders

SELECTcustomer_ID

FROMorder_t;

selecting all rows all columns
Selecting All Rows, all columns
  • SELECT *

FROM table;

  • Display information about all orders

SELECT *

FROM order_t;

selecting unique rows
Selecting Unique Rows
  • SELECT DISTINCT column(s)

FROM table;

  • List all customers who've placed orders.

SELECTDISTINCTCustomer_IDFROMOrder_t;

sorting results
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;

search conditions
Search Conditions
  • For retrieving specific rows:
    • Comparison Operators
    • Boolean Operators
    • Special Operators
    • Calculated Fields (Expressions)
  • SELECT column(s)

FROM table

WHERE <search condition(s)>;

comparison operators con t
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

comparison operators cont
Comparison Operators, cont…
  • Show order IDs and dates of all orders placed by customer 1.

SELECTOrder_ID, Order_Date

FROM Order_t

WHERE Customer_ID = 1;

  • Show the time that order #1002 was placed.

SELECTOrder_id,

CONVERT(varchar(8), Order_Date, 8) AS "Order Time"

FROM Order_t

WHEREOrder_id = 1002;

boolean operators
Boolean Operators
  • Boolean Operators
    • AND all search conditions must be met
    • OR any search condition must be met
    • NOT a search condition must not be met
  • Show orders placed by customer 1 with order ID(s) higher than 1005.

SELECTCustomer_ID, Order_ID, Order_Date

FROM Order_t

WHERECustomer_ID= 1

ANDOrder_ID> 1005;

order of evaluation
Order of evaluation
  • NOT, AND, OR
  • Use parentheses to ensure desired ordering
  • Show orders placed by CUSTOMERS EXCEPT customer 1, and with order ID(s) higher than 1007

SELECT Customer_ID, Order_ID, Order_Date

FROM Order_t

WHERE NOTCustomer_ID = 1

AND Order_ID > 1007;

  • Show orders placed by customers except customer 1, that have EITHER an order ID higher than 1007 OR an order date on the 24TH of the month

SELECT Customer_ID, Order_ID, Order_Date

FROM Order_t

WHERE NOT Customer_ID = 1

AND Order_ID > 1007 OR Day(Order_Date) = 24;

special operators
Special Operators
  • Shortcuts
    • IN
    • BETWEEN
  • Wildcard matching
    • LIKE
  • NULL
  • TOP
special operators cont
Special Operators, cont…
  • IN

Find dates that customers 1, 3, & 5 placed orders.

SELECT Customer_ID, Order_Date

FROM Order_t

WHERE Customer_ID IN (1,3,5);

  • BETWEEN

Find dates of orders placed by customers 1 thru 5.

SELECTCustomer_ID, Order_Date

FROMOrder_t

WHERECustomer_IDBETWEEN

1 AND 5;

special operators cont1
Special Operators, cont…
  • LIKE

Show customers with 'furn' in their names.

SELECTCustomer_Name

FROMCustomer_t

WHERECustomer_NameLIKE '%furn%';

Show customers with 'furn' + 5 characters in their names.

SELECTCustomer_Name

FROMCustomer_t

WHERECustomer_NameLIKE '%furn_____';

special operators cont2
Special Operators, cont…
  • Null
    • unknown
    • not applicable

List customers who do not have an address listed.

SELECT Customer_ID, Customer_Name

FROM Customer_t

WHERECustomer_addressIS NULL;

  • Beware…

SELECT Customer_Name

FROMCustomer_t

WHERECustomer_address = NULL;

special operators cont3
Special operators, cont…
  • TOP n
    • Displays first N rows of query results
    • ORDER BY not required BUT typically included

Display the first 3 orders…

SELECT TOP 3 *

FROMorder_t;

SELECT TOP 3 *

FROMorder_t

ORDER BY order_date;

 In the table???

 That we ever received???

special operators cont4
Special operators, cont…
  • TOP n WITH TIES
    • Displays query result rows that have tie/duplicate values
    • Therefore may return more than N rows
    • ORDER BY is required

Which 2 products have the highest inventory?

SELECT TOP 2product_id, product_description, qty_on_hand

FROMproduct_t

ORDER BY qty_on_handDESC;

Versus…

SELECT TOP 2 WITH TIESproduct_id, product_description, qty_on_hand

FROMproduct_t

ORDER BY qty_on_handDESC;

using column aliasing
Using Column Aliasing
  • Assign friendly names to existing columns
  • Assign names to derived columns
  • Only exists for duration of query
  • SELECTcolumn_nameAS "alias_name" [, ….]

FROM table;

  • Show order information for customers 1, 3, and 4. Label the order dates "Date of Order".

SELECTOrder_ID, Order_DateAS "Date of Order", customer_id

FROM Order_t

WHERE customer_idIN (1, 3, 4)

ORDER BY customer_id;

using table aliasing
Using table aliasing
  • Assign friendly (shortened) names to tables
  • Allows same table to be referenced multiple times in a query
  • Only exists for duration of query
  • SELECTcolumn_name(s)[, ….]

FROM table table_alias;

  • List descriptions of all products with any kind of “natural” finish.

SELECTProduct_description

FROMProduct_tP

WHEREP.product_finishLIKE 'Natural%';

expressions
Expressions
  • Manipulating column values in a query
  • Effect is Temporary
  • Show the effect of increasing product prices by 10% for those products that are currently priced under $300.

SELECTProduct_ID, Standard_PriceAS "Old Price", Standard_Price*1.1 AS "New Price"

FROMProduct_t

WHEREStandard_Price < 300;

concatenating values
Concatenating values
  • Connecting text values together
  • Show the name, full address (street, city, state, zip) and number of orders placed by each customer in Florida.

SELECTcustomer_name,

customer_address + ', ' + city + ', ' + state + ', ' +

postal_codeAS "address", orders_placed

FROMcustomer_t

WHERE state = 'fl';

sql server cast function
Sql server cast function
  • Converts data from one datatype to another
    • Use for all other datatype conversions
  • CAST ( value AS target_data_type[ ( length ) ] )
  • Examples:
      • SELECT 'The list price is: ' + CAST(standard_price AS

varchar) FROM product_t;

      • Print 'old zip: ' + CAST(@czip AS varchar)…;
functions1
Functions
  • Display, convert, manipulate values
  • Scalar
    • Text
    • Date/Time
      • Year, Month, Day
    • Mathematical
      • Floor, Ceiling, Round
      • ….
    • System
      • System_User
      • GetDate()
  • Aggregate
    • COUNT
    • MIN
    • MAX
    • SUM
    • AVG
dates
DATES
  • Work with dates in ways other than mm/dd/yy
    • Can also use CONVERT function to do this
  • General Information on SQL Server Date functions:
    • http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx
    • http://www.w3schools.com/SQl/sql_dates.asp
  • Examples of using Date functions
    • http://jahaines.blogspot.com/2009/06/date-functions.html
    • http://sqlserverpedia.com/wiki/Built-in_Functions_-_Date_and_Time_Functions
dates cont
Dates, cont…
  • Show the year customer 1 last placed an order.

SELECTTOP 1YEAR(order_date) AS "Year Last Ordered"

FROMorder_t

WHEREcustomer_id = 1

ORDER BY order_dateDESC;

  • Show the different months that orders were placed.

SELECT DISTINCTMONTH(order_date)

FROMorder_t;

SELECT DISTINCT DATENAME(month, order_date) AS "Month"

FROMorder_t;

dates cont1
Dates, cont…
  • How many months have elapsed between our order dates and today?

SELECT DATEDIFF(MONTH, order_date, GETDATE())

FROMorder_t;

* syntax is DATEDIFF(datepart, start_date, end_date)

** see http://www.w3schools.com/sql/func_datediff.asp for examples of how to subtract other date parts such as times

  • Show how old, in years, our orders are. Show results in descending order of age.

SELECTorder_id,YEAR(getdate()) - YEAR(order_date) AS "Order Age in Years"

FROMorder_t

ORDER BY "Order Age in Years" DESC;

numeric
numeric
  • Performs mathematical functions
  • Show the effects of decreasing product prices by 3.5%.

SELECTproduct_id, standard_price, standard_price*.965

FROMproduct_t;

SELECTproduct_id, standard_price, ROUND(standard_price*.965, 2)

FROMproduct_t;

SELECTproduct_id, standard_price, FLOOR(standard_price*.965)

FROMproduct_t;

system
SYSTEM
  • Retrieves information maintained by SQL Server
  • Who is the user currently logged on?

SELECTSYSTEM_USER;

  • What is today's date?

SELECTGETDATE();

  • What orders were placed in the current month of any year?

SELECT *

FROMorder_t

WHERE MONTH(order_date) = MONTH(GETDATE());

aggregate functions1
Aggregate Functions
  • Produce aggregated data
    • COUNT
    • MIN
    • MAX
    • SUM
    • AVG
count
Count
  • Counts number of rows/values retrieved from query
    • How many orders are there?

SELECT COUNT(*) FROMOrder_t;

    • Show how many orders were placed after Nov 1st 2000.

SELECT COUNT(*)

FROMOrder_t

WHEREOrder_Date > '01-NOV-00';

    • How many orders have customer IDs?

SELECT COUNT (Customer_ID) FROMOrder_t;

    • How many different customers have placed orders?

SELECT COUNT (DISTINCTCustomer_ID)

FROMOrder_t;

min and max
Min and Max
  • Finds minimum/maximum value of attribute

Find date of most recent order.

SELECT MAX(Order_Date) FROMOrder_t;

Show the earliest year that an order was ever placed.

SELECTMIN (YEAR(Order_Date))

FROMOrder_t;

Show the date that customer 1 first placed an order.

SELECTMIN(Order_Date)

FROM Order_t

WHERE Customer_ID= 1;

sum and avg
Sum and Avg
  • SUM (totals values for specific attribute)

How many products are in inventory?

SELECT SUM(qty_on_hand) FROMProduct_t;

  • AVG (finds average value of an attribute)

What's the average price of our products?

SELECT AVG(Standard_Price) FROMProduct_t;

List the average product price and lowest product quantity on hand of our products.

SELECT AVG(Standard_Price),MIN(qty_on_hand) FROM Product_t;

categorizing results
Categorizing Results
  • GROUP BY
    • groups output according to an attribute
    • can perform operations on groups of rows
  • List the average product price and lowest product quantity on hand for each product finish we offer.

SELECTProduct_finish, AVG(Standard_Price), MIN(qty_on_hand)

FROM Product_t;

SELECT Product_finish,AVG(Standard_Price),MIN(qty_on_hand)

FROMProduct_t

GROUP BYProduct_finish;

more on group by s
More on Group By’s…
  • How many customers are there in each state?

SELECT COUNT(customer_id)

FROM customer_t GROUP BY state;

SELECT COUNT(customer_id), state

FROM customer_t GROUP BY state;

  • How many customers are in each postal_code area of each state?

SELECT COUNT(customer_id), state, postal_code

FROM customer_t

GROUP BY state;

SELECT COUNT(customer_id), state, postal_code

FROM customer_t

GROUP BY state, postal_code;

categorizing results con t
Categorizing Results, con't...
  • HAVING
    • Like a WHERE clause for groups

List the average product price and lowest product quantity on hand for each product finish that has (a) at least one product with two or more units on hand, and(b) at least 3 products with that finish.

SELECT Product_finish, AVG(Standard_Price), MIN(Qty_on_hand)

FROM Product_t

GROUP BY Product_finish

HAVING MIN (Qty_On_hand) >= 2

AND COUNT (Product_id) >=3;

querying multiple tables
Querying Multiple Tables
  • Nested Queries (aka Subqueries)
  • Joins
    • (Inner) Join
    • Self Join
    • (Outer) Left Join
  • Checking Non-Existence Conditions
    • NOT IN
    • NOT EXISTS
  • Creating Views of data
subqueries
subqueries
  • Placing an "inner" query in WHERE clause
  • Inner queries must SELECT only one column
  • Column selected by "inner" query must match column in WHERE clause of “outer" query

Show all orders placed by customers who live in states that do NOT end in 'A'. Show orders in descending order.

SELECTOrder_ID

FROMOrder_t

WHERECustomer_IDIN

(SELECTCustomer_ID

FROMCustomer_t

WHERE State NOT LIKE '_A')

ORDER BYOrder_IDDESC;

subqueries cont
Subqueries, cont…

Which customers have not placed orders? Show customer IDs and names, sorted by name.

SELECT customer_id, customer_name

FROMcustomer_t

WHERE customer_idNOT IN

(SELECT _____________

FROM order_t)

ORDER BY customer_name;

subqueries cont1
Subqueries, cont…

Revisit: Which 2 products have the highest inventory?

SELECT TOP 2WITH TIES product_id, product_description,

qty_on_hand

FROMproduct_t

ORDER BY qty_on_handDESC;

SELECTproduct_id, product_description,

qty_on_hand

FROM Product_t

WHEREqty_on_handIN

(SELECT DISTINCT TOP 2 qty_on_hand

FROMproduct_t

ORDER BYqty_on_handDESC);

joins
joins
  • Bring data together from multiple rows/tables
    • Use common column in both rows/tables
    • Common columns can have same or different names
    • Must use table_name.column_name to distinguish columns with same names
    • Can specify one or multiple tables to connect
    • Use WHERE or FROM clause to connect tables
  • Several Types
    • Cross Join
    • (Inner) Join
    • Self Join
    • (Outer) Left Join
    • (Outer) Right Join
    • (Outer) Full Join
  • See http://www.quackit.com/sql/tutorial/sql_join.cfm
inner join
(Inner) join
  • Can use WHERE or FROM clause
  • Retrieve Customer Names, Customer IDs, and Order Dates of orders placed after Nov 1, 2000.

SELECT Customer_t.Customer_ID,

Customer_Name, Order_date

FROMCustomer_t, Order_t

WHEREOrder_date> '01-NOV-00'

ANDCustomer_t.Customer_ID= Order_t.Customer_ID;

inner joins con t
(Inner) joins, con't…
  • List descriptions of all products ordered by customer 1.

SELECT Product_description

FROM Order_Line_t, Order_t, Product_t

WHERE Order_t.Customer_ID = 1

AND Order_t.Order_ID = Order_Line_t.Order_ID

AND Order_Line_t.Product_ID = Product_t.Product_ID;

inner joins con t1
(inner) joins, con't…
  • List descriptions of all products ordered by customer 1.

SELECTProduct_description

FROM (Order_Line_tolINNER JOIN Order_t o

ON ol.Order_ID = o.Order_ID)

INNER JOIN Product_t p

ONol.Product_ID = p.Product_ID

WHEREo.Customer_ID = 1;

inner joins con t2
(Inner) joins, con't…
  • Let's combine concepts!

Assuming all orders need to be filled, which products do not have enough quantity on hand to fill their orders?

Show the product ID, description, the quantity of the product on hand, and the total quantity of the product ordered.

self joins
Self joins
  • Join a table to itself
  • Rows from the same table are related
    • i.e., recursive relationships
    • Primary and Foreign keys in same table, different rows
self joins con t
Self joins, con't…
  • For each customer who has an owner, show the customer's name and their owner's name.

CUSTOMER_T

self joins con t2

c.

id

c.name

c.addr

c.city

c.state

c.postal_cd

c.ownid

o.id

o.name

o.addr

o.city

o.state

o.postal_cd

o.ownid

1

contemporary casuals

1355 s. hines blvd

gainesville

fl

32601

10

10

seminole interiors

2400 rocky point dr.

seminole

fl

34646

2

10

seminole interiors

2400 rocky point dr.

seminole

fl

34646

2

2

value furniture

15145 x.w.17th st.

plano

tx

75094

6

furniture gallary

325 flatiron dr.

boulder

co

80514

10

10

seminole interiors

2400 rocky point dr.

seminole

fl

34646

2

5

impressions

5585 westcott ct.

sacremento

ca

94206

2

2

value furniture

15145 x.w.17th st.

plano

tx

75094

14

kaneohe homes

112 kiowai st.

kaneohe

hi

96744

10

10

seminole interiors

2400 rocky point dr.

seminole

fl

34646

2

8

california classics

816 peach rd.

santa clara

ca

96915

15

15

mountain scenes

Self joins, con't…
  • For each customer who has an owner, show the customer's name and their owner's name.
self joins con t3
Self joins, con't…
  • For each customer who has an owner, show the customer's name and their owner's name.

SELECTC.Customer_nameAS "Customer", O.Customer_nameAS "Owner"

FROMCustomer_t C, Customer_t O

WHEREC.Owner_ID = O.Customer_ID;

outer joins
Outer joins
  • Left (Outer) Joins
    • Brings data together from multiple rows/tables
      • Use common column in both rows/tables
      • Retrieves all rows from first table, and only matching rows from second table
      • Use table_name.column_name to distinguish columns
      • Can specify TWO tables at a time to connect
      • Use FROM clause to connect tables
    • List products that have not been ordered

(include product IDs and descriptions)

SELECTp.Product_ID, p.Product_Description

FROMProduct_t p LEFT JOIN Order_line_tol

ON p.Product_ID = ol.Product_ID

WHEREol.Product_IDIS NULL;

querying multiple tables con t
Querying Multiple Tables, con't…
  • Derived Tables
    • Placing an "inner" query in the FROM clause
    • Must use table alias for derived table
  • SELECT col(s)

FROM (SELECT col(s) FROM table(s) WHERE…) tblalias

[WHERE …];

  • Retrieve Customer Names, Customer IDs, and Order Dates of orders placed after Nov 1, 2000.

SELECT xyz.customer_id, xyz.customer_name, xyz.order_date

FROM (SELECT c.Customer_ID, c.Customer_Name, Order_Date

FROM Customer_t c, Order_t o

WHERE Order_date > '01-NOV-00'

AND c.Customer_id = o.Customer_id) XYZ;

Table Alias REQUIRED!

querying multiple tables con t1
Querying Multiple Tables, con't…
  • Derived Tables, cont…
  • What product finish has the highest average price?

SELECT Product_finish, AVG(Standard_Price)

FROM Product_t

GROUP BY Product_finish

HAVING AVG(Standard_Price) =

(SELECT MAX(AVG(Standard_Price))

FROM Product_t

GROUP BY Product_finish);

querying multiple tables con t2
Querying Multiple Tables, con't…
  • Derived Tables, cont…
  • What product finish has the highest average price?

SELECT Product_finish, AVG(Standard_Price)

FROM Product_t

GROUP BY Product_finish

HAVING AVG(Standard_Price) =

(SELECT MAX(Price)

FROM (SELECT AVG(Standard_Price) AS Price FROM Product_t

GROUP BY Product_finish)

TempTbl);

Table Alias REQUIRED!

views
VIEWs
  • Dynamic vs. "Materialized"
  • Why use views?
    • Simplify queries
    • Rename columns
    • Security
  • CREATE VIEW view_name AS

SELECT …… ;

views cont
VIEWs, cont…

Retrieve all the data elements required to create a customer invoice

SELECTc.customer_id, customer_address, o.order_id, p.product_id, product_description, standard_price, ordered_quantity, standard_price*ordered_quantityAS "line item cost"

FROMcustomer_t c, order_t o, order_line_tol, product_t p

WHERE c.customer_id = o.customer_id

AND o.order_id = ol.order_id

ANDol.product_id = p.product_id;

Create a view that retrieves all the data elements required to create a customer invoice

CREATE VIEW invoice_viewAS

SELECTo.customer_id, customer_address, ol.order_id, ol.product_id, product_description, standard_price, ordered_quantity as quantity, standard_price*ordered_quantityAS "line item cost"

FROMcustomer_t c, order_t o, order_line_tol, product_t p

WHERE c.customer_id = o.customer_id

AND o.order_id = ol.order_id

ANDol.product_id = p.product_id;

slide104
CASE
  • Analogous to IF-THEN-ELSE within SQL
  • Examples:
    • SELECTproduct_id, CASE

WHENproduct_id = 3 THENorder_id

WHENproduct_id = 8 THENproduct_id

ELSEordered_quantity

END

AS "order#, product#, or QN"

FROMorder_line_t;

    • SELECT CASE WHEN state = 'FL' THENpostal_codeEND

FROMcustomer_t

WHEREcustomer_id <> 9;

  • See:
    • http://www.java2s.com/Code/Oracle/Select-Query/CombineCasewithgroupby.htm
    • http://www.adp-gmbh.ch/ora/sql/case_when.html
    • http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm (syntax for different dbms's)
summary
Summary
  • SQL Server Architecture
    • Components
    • Where user data and metadata are stored
    • How applications fit in
  • Types of SQL Server Products
    • Our focus is on the query services and the RDBMS
  • How to access SQL Server for class
  • History of SQL
  • SQL Environment
  • SQL Language
    • DDL
    • DML
      • Basic Query
      • Search Conditions
      • Categorizing and Sorting Results
      • Querying Multiple Tables
        • Various flavors of Joins
        • Various Flavors of Subqueries
      • Additional SQL commands