Week 4 Flashcards Preview

Database Systems > Week 4 > Flashcards

Flashcards in Week 4 Deck (35)
Loading flashcards...
1
Q

2 Major components in SQL?

A
  1. DDL for defining database structure

2. DML for retrieving and updating data

2
Q

Create table?

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

insert into?

A

INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);

4
Q

SQL Commands rules?

A
  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
Q

DISTINCT

A

SELECT DISTINCT propertyNo

FROM Viewing

6
Q

WHERE

A

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

7
Q

WHERE OR

A

SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’

8
Q

BETWEEN

A

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

9
Q

IN

A

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

10
Q

Pattern Matching, LIKE, %

A

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

11
Q

IS NULL, IS NOT NULL

A

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

12
Q

ORDER BY DESC

A

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

13
Q

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

A

YES

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

14
Q

what COUNT(*) do?

A

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

15
Q

Aggregate functions can be used only in?

A

SELECT and HAVING clause

16
Q

COUNT(*)

A

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

17
Q

COUNT(DISTINCT)

A

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

18
Q

SELECT clause may only contain?

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

GROUP BY

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

HAVING

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

Perbedaan ANY dan ALL

A

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
Q

SIMPLE JOIN

A

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

23
Q

THREE TABLE JOIN

A

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
Q

Left Outer Join

A

SELECT b., p.

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

25
Q

Right Outer Join

A

SELECT b., p.

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

26
Q

Full Outer Join

A

SELECT b., p.

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

27
Q

EXISTS

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

UNION

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

INTERSECT

A

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

30
Q

EXCEPT

A

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

31
Q

INSERT INTO

A

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

32
Q

UPDATE

A

UPDATE Staff

SET salary = salary*1.03

33
Q

UPDATE Multiple Columns

A

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

34
Q

DELETE FROM

A

DELETE FROM Viewing

WHERE propertyNo = ‘PG4’

35
Q

Delete all records from Viewing Table

A

DELETE FROM Viewing