Database

In my junior year I enrolled in a database design class. We started off developing entity relationship diagrams and learning the basics of databases. This progressed to learning and practicing SQL queries.

Over the course we learned the design and implementation of database systems. This course had an emphasis on relational database models, concepts like: normalization, ER modeling, locking, SQL, and distributed databases.

Below are attached SQL snippets as well as entire solutions:

• Create and Alter Table Statements

This assignment practiced the use of create and alter table statements to add PKs and FKs.

--Create Video Table
CREATE TABLE VIDEO_A
(
VID__NUM INT,
VID_INDATE DATETIME,
MOVIE_NUM INT
);

--PK and FK to Video
ALTER TABLE VIDEO_A
ADD PRIMARY KEY (VID_NUM)
ALTER TABLE VIDEO_A
ADD FOREIGN KEY (MOVIE_NUM)
REFERENCES MOVIE_A(MOVIE_NUM)
);

• Insert, Delete, Update Trigger

This assignment practiced triggers with different scenarios to inset, delete, or update a customer balance.

UPDATE      CUSTOMER
SET      CUS_BALANCE = 0

DECLARE @CUS_NUM CHAR(3)
DECLARE @BALANCE_SUM DECIMAL(2)

IF(EXISTS(SELECT*FROM ORDERS))
BEGIN

	DECLARE INSERTED_CURSOR CURSORFOR
	SELECT CUSTOMER_NUM,SUM(NUM_ORDERED * QUOTED PRICE)AS BALANCE_SUM
	FROM ORDERS INNERJOIN ORDER_LINE
	GROUPBY CUSTOMER_NUM
	OPEN INSERTED_CURSOR
	FETCH NEXT FROM INSERTED_CURSOR
	INTO @CUS_NUM, @BALANCE_SUM
	WHILE(@@FETCH_STATUS= 0)
	BEGIN
		UPDATE CUSTOMER
		SET      CUS_BALANCE = 0
		WHERE  CUS_NUM = @CUS_NUM
		FETCH NEXT FROM INSERTED_CURSOR
				INTO @CUS_NUM, @BALANCE_SUM
		END

• Variety of SQL Skills

This assignment worked on a variety of SQL skills. Below is a snippet of the answer to the following question: “Which merchandise items with more than 100 units on hand have not been ordered in 2004? Use an outer join to answer the question.”

SELECT M.ItemID, M.Description, M.QuantityOnHand, MO.OrderDate
FROM PET..Merchandise M
FULL OUTER JOIN PET..OrderItem OI ON M.ItemID = OI.ItemID
FULL OUTER JOIN PET..MerchandiseOrder MO ON OI.PONumber = MO.PONumber
WHERE M.QuantityOnHand > 100
AND M.ItemID NOTIN(SELECT M.ItemID
	FROM PET..Merchandise M
	WHEREYEAR(MO.OrderDate)= 2004)