Download SQL Lecture 3: SQL Basics, Joins and Queries and more Slides Data Structures and Algorithms in PDF only on Docsity!
1.204 Lecture 3
SQL: Basics, Joins
SQL
- Structured query language (SQL) used for
e b
finition (DDL): tables and views (virtual tables). These are the basiic operations to convert a data model to a ti t t d t d l t database
- Data manipulation (DML): user or program can INSERT, DELETE, UPDATE or retrieve (SELECT) data.
- Data integrity: referential integrity and transactions. Enforces keys.
- Access control: security
- Data sharing: by concurrent users
- NotNot a completea complete language like Javalanguage like Java
- SQL is sub-language of about 30 statements
- Nonprocedural language
- No branching or iteration
- Declare the desired result, and SQL provides it
SQL SELECT
• SELECT constructed of clauses to get
columns and rows from one or more
SELECT constructed of clauses to get
tables or views. Clauses must be in order:
– SELECT columns/attributes
– INTO new table
– FROM table or view
– WHEREWHERE specific rows orspecific rowsor aa join is createdjoin is created
– GROUP BY grouping conditions (columns)
– HAVING group-property (specific rows)
– ORDER BY ordering criterion ASC | DESC
Orders
OrderNbr Cust Prod Qty Amt Disc 1 211 Bulldozer 7 $31,000.00 0. 2 522 Riveter 2 $4,000.00 0. 3 522 Crane 1 $500,000.00 0.
CustNbr Company CustRep CreditLimit 211 Connor Co 89 $50,000. 522 AmaratungaEnterprise 89 $40,000. 890 Feni Fabricators 53 $1,000,000.
RepNbr Name RepOffice Quota Sales 53 Bill Smith 1 $100 000 00 $0 00
Customers
SalesReps
53 Bill Smith 1 $100,000.00 $0. 89 Jen Jones 2 $50,000.00 $130,000.
Offices
p
OfficeNbr City State Region Target Sales Phone
1 Denver CO West $3,000,000.00 $130,000.00 970.586. 2 New York NY East $200,000.00 $300,000.00 212.942. 57 Dallas TX West $0.00 $0.00 214.781.
Example tables
SQL queries: SELECT
• ClickClick ‘NewNew QueryQuery’ inin MSE;MSE; tytypepe thesethese statements:statements:
• List the sales reps
- SELECT Name, Sales, Quota FROM SalesReps
• Find the amount each rep is over or under quota
- SELECT Name, Sales, Quota, (Sales-Quota) FROM
SalesReps
• Find the slackers
- SELECT Name, Sales, Quota, (Sales-Quota) FROM
SalesReps WHERE Sales < Quota
RepNbr Name RepOffice Quota Sales 53 Bill Smith 1 $100,000.00 $0. 89 Jen Jones 2 $50,000.00 $130,000.
SQL queries: calculation, insert,
delete, update
- Find the average sale
- SELECTSELECT AAVVG(G(AAmt)mt) FFROMROM OOrders;rders;
- Find the average sale for a customer
- SELECT AVG(Amt) FROM Orders WHERE Cust = 211;
- Add an office
- INSERT INTO Offices (OfficeNbr, City, State, Region, Target, Sales, Phone) VALUES (‘55’, ‘Dallas’,‘TX’,‘West’, 200000, 0, ‘214.333.2222’);
- Delete a customer
- DELETE FROM Customers WHERE Company = ‘Connor Co’;
- (Syntax is valid but command will fail due to referential integrity)
- Raise a credit limit
- UPDATE Customers SET CreditLimit = 75000 WHERE Company = ‘Amaratunga Enterprises’;
SELECT: * and duplicates
• • Select all columns (fields)Select all columns (fields)
– SELECT * FROM Offices;
• Duplicate rows: query will get two
instances of ‘West’
– SELECT Region FROM Offices;
• • Eliminate duplicates:Eliminate duplicates:
– SELECT DISTINCT Region FROM Offices;
NULLs
• NULL values evaluate to NOT TRUE in all cases.
- Insert ‘NewRep’ with NULL (blank or empty) Quota
• The following two queries will not give all sales reps:
- SELECT Name FROM SalesReps WHERE Sales > Quota;
- SELECT Name FROM SalesReps WHERE Sales <= Quota;
- A new rep with a NULL quota will not appear in either list
• Check for NULLS byy:
- SELECT Name FROM SalesReps WHERE Quota IS NULL;
Examples
• What is the average credit limit of customersWhat
whose
is
credit limit
the average
is less than $1,000,000?
credit limit of customers
- SELECT AVG(CreditLimit) FROM Customers WHERE
CreditLimit < 1000000;
• How many sales offices are in the West region?
- SELECT Count(*) FROM Offices WHERE Region= 'West‘;
• Increase the price of bull
orders
dozers by 30% in all
orders
- UPDATE Orders SET Amt= Amt*1.3 WHERE Prod=
'Bulldozer‘;
• Delete any sales rep with a NULL quota
- DELETE FROM SalesReps WHERE Quota IS NULL;
Joins
• Relational model permits you to bring data from
separate tables into
relationships
new and unanticipated
relationships.
• Relationships become explicit when data is
manipulated: when you query the database, not
when you create it.
- This is critical; it allows extensibility in databases.
- You can join on any columns in tables, as long as data
need
types
to
match and the operation
be keys though they usually
makes sense. They
are
don’t
need to be keys, though they usually are.
• Good joins
- Join columns must have compatible data types
- Join column is usually key column:
- Either primary key or foreign key
- Nulls will never join
Joins
- List all orders, showing order number and amount, and name and credit limit of customer - Orders has order number and amount, but no customer names or credit limits - Customers has customer names and credit limit, but no order info
- SELECTSELECT OOrderderNbr,rNbr, AmAmtt,, CompanyCompany,, CreditLimitCreditLimit FFROMROM CCuustomers,stomers, Orders WHERE Cust = CustNbr; (Implicit syntax)
- SELECT OrderNbr, Amt, Company, CreditLimit FROM Customers INNER JOIN Orders ON Customers.CustNbr = Orders.Cust; (SQL-92)
OrderNbr Cust Prod Qty Amt Disc 1 211 Bulldozer 7 $31,000.00 0. 22 522522 RiRiveter t 22 $4 000 00$4,000.00 0 30. 3 522 Crane 1 $500,000.00 0.
Join CustNbr^ Company^ CustRep^ CreditLimit
211 Connor Co 89 $50,000. 522 Amaratunga Enterprises 89 $40,000. 890 Feni Fabricators 53 $1,000,000.
Join with 3 tables
- List orders over $25,000, including the name of the salesperson who took the order and the name of the customer who placed it. - SELECT OrderNbr, Amt, Company, Name FROM Orders, Customers, SalesReps WHERE Cust = CustNbr AND CustRep = RepNbr AND Amt >= 25000; (Implicit syntax)
CustNbr Company CustRep CreditLimit 211 Connor Co 89 $50,000. 522 Amaratunga Enterprises 89 $40,000. $
OrderNbr Cust Prod Qty Amt Disc 1 211 Bulldozer 7 $31,000.00 0. 2 522 Riveter 2 $4,000.00 0. 3 522 Crane 1 $500,000.00 0.
Join
RepNbr Name RepOffice Quota Sales 53 Bill Smith 1 $100,000.00 $0. 89 Jen Jones 2 $50,000.00 $130,000.
890 Feni Fabricators 53 $1,000,000.
OrderNbr Amt Company Name 1 $31,000.00 Connor Co Jen Jones 3 $500,000.00 AmaratungaEnterprise Jen Jones
Result:
Join
Examples
- List customer names whose credit limit is g
sales rep’s quota. Also list the credit limit and
greater than their
quota.
- SELECT CreditLimit, Quota, Company FROM SalesReps INNER JOIN Customers ON SalesReps.RepNbr = Customers.CustRep WHERE CreditLimit>Quota;
- List each rep’s name and phone number
- SELECT Name, Phone FROM Offices INNER JOIN SalesReps ON Offices.OfficeNbr = SalesReps.RepOffice;
- Display all customers with orders or credit limits > $50,000.
- SELECT DISTINCT CustNbr FROM Customers LEFT JOIN Orders ON CustNbr = Cust WHERE (CreditLimit > 50000 OR Amt > 50000)
