Week 4 Flashcards
2 Major components in SQL?
- DDL for defining database structure
2. DML for retrieving and updating data
Create table?
CREATE TABLE Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2));
insert into?
INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);
SQL Commands rules?
- uppercase letters represent reserved words
- lowercase letters represent user-defined words
- | indicates a choice among alternatives
- curly braces indicate a required element
- square braces indicate an optional element
- … indicates optional repetition (0 or more)
DISTINCT
SELECT DISTINCT propertyNo
FROM Viewing
WHERE
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000
WHERE OR
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’
BETWEEN
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000
IN
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’)
Pattern Matching, LIKE, %
SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’
IS NULL, IS NOT NULL
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND comment IS NULL
ORDER BY DESC
SELECT stafNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC
COUNT, MIN, and MAX can apply to non-numeric?
YES
Only SUM and AVG may be used on numeric fields only.
what COUNT(*) do?
counts all rows of a table, regardless of whether nulls or duplicate values occur
Aggregate functions can be used only in?
SELECT and HAVING clause
COUNT(*)
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350
COUNT(DISTINCT)
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-13’ AND ‘31-May-13’
SELECT clause may only contain?
- column names
- aggregate functions
- constants
- expression involving combinations of the above
GROUP BY
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySUM
FROM Staff
GROUP BY branchNo
ORDER BY branchNoHAVING
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySUM
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNoPerbedaan ANY dan ALL
ANY and ALL may be used with subqueries that produce a single column of numbers
ALL, condition will only be true if it is satisfied by ALL values produced by subquery
ANY, condition will be true if it is satisfied by ANY values produced by subquery
if subquery is empty, ALL returns true, ANY returns false
SIMPLE JOIN
SELECT c.clientNo, fName, lName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo
THREE TABLE JOIN
SELEC b.branchNo, b.city, s.staffNo, fName, lName
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo
Left Outer Join
SELECT b., p.
FROM Branch1 b LEFT JOIN PropertyForRent1 ON b.bCity = p.pCity
