Unit 11

Question 1

How to select something in SQL?

Answer 1

SELECT
FROM
WHERE
ORDER BY

Question 1

How to create a table in SQL?

Answer 1

CREATE TABLE tblProduct
(
ProductID CHAR(4) NOT NULL PRIMARY KEY,
Description VARCHAR(20) NOT NULL,
Price CURRENCY
)

Question 2

How to add a new column to a table in SQL?

Answer 2

ALTER TABLE tblProduct
ADD QtyInStock INTEGER

Question 3

How to delete a column in SQL?

Answer 3

ALTER TABLE tblProduct
DROP QtyInStock

Question 4

How to change the type/ length of a column?

Answer 4

ALTER TABLE tblProduct
MODIFY COLUMN Description VARCHAR (30) NOT NULL

Question 5

How to define linked tables?

Answer 5

CREATE TABLE ProductComponent // creating table
( ProductID CHAR(4) NOT NULL,
CompID CHAR(6) NOT NULL,
Quantity INTEGER,
FOREIGN KEY ProductID REFERENCES Product(ProductID), //enforcing the relashonships
FOREIGN KEY CompID REFERENCES Component(CompID),
PRIMARY KEY (ProductID, CompID) )

Question 6

How to insert data into a table?

Answer 6

INSERT INTO Product(ProductID, Description, Price) //note only put the ones you want to add data into
VALUES (“A345”, “Pink Rabbit”, 7.50)

Question 7

How to update data in a table?

Answer 7

UPDATE Product
SET Description = “Blue Rabbit”, Price = 8.25
WHERE ProductID = “A345”

Question 8

How to delete a record in SQL?

Answer 8

DELETE FROM Product
WHERE ProductID = “A345”

Question 9

What is first normal form?

Answer 9

A data base will be in 1nf is it has no repearting attributes or grouped attributes

Question 10

What is second normal form?

Answer 10

A database will be in 2nf if it is already in 1nf and is in a state where all of its non key attributes(columns) are dependant on/ related to the primary key.

Question 11

What is 3rd normal form?

Answer 11

A database will be in 3nf it is alredy in 2nf and contains no attribbutes that (in addition to the primary key) ALSO depend on another attribute in the table.