4.10.4 Structured Query Language (SQL)

Question 1

How do you define a database table in SQL?

Answer 1

CREATE TABLE TableName (
StringAttribute VARCHAR(MaxNumOfCharas)
CHARACTER(FixedNumOfCharas) PRIMARY KEY
NumAttribute INT / REAL / FLOAT / DATE / TIME / SMALLMONEY / MONEY
BoolAttribute BOOLEAN
CONSTRAINT constraint_name FOREIGN KEY (fk_name_here) REFERENCES tableFKisFrom(fk_name)
ON DELETE CASCADE / RESTRICT / SET NULL / SET DEFAULT / NO ACTION
ON UPDATE CASCADE
)

Question 2

How do you retrieve data in SQL?

Answer 2

SELECT AttributesToDisplay1, SUM(IntAttribute), etc. [* for all ]
FROM TableName1, TableName2, etc.
WHERE Attribute = “A”
AND Date >= “01/01/2014”
OR TableName1.ID = TableName2.ID
OR AttributeString LIKE ‘T%’ [starts with T]

ORDER BY Attribute (ASC / DESC)
LIMIT MaxNumberOfItemsToDisplay;

Question 3

How do you update data in SQL?

Answer 3

UPDATE TableName
SET Attribute = “updatedinfo”
WHERE OtherAttribute = “1”

Question 4

How do you insert a record in SQL?

Answer 4

INSERT INTO TableName (attributestoAdd, …)

VALUES (FirstAttribute,SecondAttribute, etc.);

Question 5

How do you delete data in SQL?

Answer 5

DELETE FROM TableName
WHERE Attribute = “1”;

DELETE * FROM TableName

DROP TABLE tabName;

Question 6

How do you alter tables?

Answer 6

ALTER TABLE tabName 
ADD COLUMN name VARCHAR
CHANGE COLUMN name newName variables
DROP COLUMN name
DROP PRIMARY / FOREIGN KEY
ADD CONSTRAINT name

Question 7

What do the joins do?

Answer 7

SELECT * FROM A CROSS JOIN B

• returns all rows of A and B, same as cross product
• equivalent to SELECT * FROM A, B

SELECT * FROM A INNER JOIN B
ON condition
USING (col1, col2)
- returns pair of rows satisfying a condition
- joins on col1 and col2
- output rows where col1 and col2 values are the same

SELECT * FROM A NATURAL JOIN B
- like inner join but combining all values that are the same in columns of the same name

SELECT * FROM A (LEFT / RIGHT FULL) OUTER JOIN B

• same as inner join with conditions, but also handles NULLS
• fills in the blanks with NULLS
• left returns every row in A but where B has no corresponding data it has NULLS
• right does the oppositie for B
• full join returns all the data