Week 4 Flashcards Preview

Database Systems > Week 4 > Flashcards

Flashcards in Week 4 Deck (35):
1

2 Major components in SQL?

1. DDL for defining database structure
2. DML for retrieving and updating data

2

Create table?

CREATE TABLE Staff(staffNo VARCHAR(5),
lName VARCHAR(15),
salary DECIMAL(7,2));

3

insert into?

INSERT INTO Staff VALUES ('SG16', 'Brown', 8300);

4

SQL Commands rules?

1. uppercase letters represent reserved words
2. lowercase letters represent user-defined words
3. | indicates a choice among alternatives
4. curly braces indicate a required element
5. square braces indicate an optional element
6. ... indicates optional repetition (0 or more)

5

DISTINCT

SELECT DISTINCT propertyNo
FROM Viewing

6

WHERE

SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000

7

WHERE OR

SELECT *
FROM Branch
WHERE city = 'London' OR city = 'Glasgow'

8

BETWEEN

SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000

9

IN

SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN ('Manager', 'Supervisor')

10

Pattern Matching, LIKE, %

SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE '%Glasgow%'

11

IS NULL, IS NOT NULL

SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = 'PG4' AND comment IS NULL

12

ORDER BY DESC

SELECT stafNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC

13

COUNT, MIN, and MAX can apply to non-numeric?

YES
Only SUM and AVG may be used on numeric fields only.

14

what COUNT(*) do?

counts all rows of a table, regardless of whether nulls or duplicate values occur

15

Aggregate functions can be used only in?

SELECT and HAVING clause

16

COUNT(*)

SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350

17

COUNT(DISTINCT)

SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN '1-May-13' AND '31-May-13'

18

SELECT clause may only contain?

1. column names
2. aggregate functions
3. constants
4. expression involving combinations of the above

19

GROUP BY

SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySUM
FROM Staff
GROUP BY branchNo
ORDER BY branchNo

20

HAVING

SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySUM
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo

21

Perbedaan 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

22

SIMPLE JOIN

SELECT c.clientNo, fName, lName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo

23

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

24

Left Outer Join

SELECT b.*, p.*
FROM Branch1 b LEFT JOIN PropertyForRent1 ON b.bCity = p.pCity

25

Right Outer Join

SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN PropertyForRent1 ON b.bCity = p.pCity

26

Full Outer Join

SELECT b.*, p.*
FROM Branch1 b FULL JOIN PropertyForRent1 ON b.bCity = p.pCity

27

EXISTS

SELECT staffNo, fName, lName, position
FROM Staff s
WHERE EXISTS (
SELECT *
FROM Branch b
WHERE s.branchNo = b.branchNo AND city = 'London')

28

UNION

(SELECT city
FROM Branch
WHERE city IS NOT NULL)
UNION
(SELECT city
FROM PropertyForRent
WHERE city IS NOT NULL)

29

INTERSECT

(SELECT city FROM BRANCH)
INTERSECT
(SELECT city FROM PropertyForRent)

30

EXCEPT

(SELECT city FROM Branch)
EXCEPT
(SELECT city FROM PropertyForRent)

31

INSERT INTO

INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo) VALUES ('SG44', 'Anne', 'Jones', Assistant', 8100, 'B003')

32

UPDATE

UPDATE Staff
SET salary = salary*1.03

33

UPDATE Multiple Columns

UPDATE Staff
SET position = 'Manager', salary = 18000
WHERE staffNo = 'SG14'

34

DELETE FROM

DELETE FROM Viewing
WHERE propertyNo = 'PG4'

35

Delete all records from Viewing Table

DELETE FROM Viewing