Queries in the Enrayle's Party Planning Lesson

CREATE TABLE Customer
(
CustID int PRIMARY KEY,
Lastname varchar(50) NOT NULL,
Firstname varchar(50) NOT NULL,
Middlename varchar(50) NOT NULL,
CustAddress varchar(50),
ContactNumber varchar(50),
Email varchar(50)
);

CREATE TABLE Party
(
PartyID int PRIMARY KEY,
PartyName varchar(50) NOT NULL,
PartyDate varchar(50) NOT NULL,
Nature_ofParty varchar(50),
PartyAddress varchar(50) NOT NULL,
CustID int NOT NULL
);

CREATE TABLE Food
(
FoodID int PRIMARY KEY,
FoodName varchar(50) NOT NULL,
FoodType varchar(50)
);


CREATE TABLE Menu
(
MenuID int PRIMARY KEY,
PartyID int NOT NULL,
FoodID int NOT NULL,
No_Servings int NOT NULL
);

INSERT INTO Customer
(CustID, Firstname, Lastname, Middlename,
CustAddress, ContactNumber, Email)
VALUES
('1','Jhosalie','Ardena','Uy',
'Pavia','12345','jhosaardena@yahoo.com'),
('2','RK','Astronomia','Eusuya',
'Roxas','54321','RKEusuya@gmail.com'),
('3','Striker','Aspero','Macasio',
'Pasig','67891','strikermacasio@yahoo.com');

INSERT INTO Party
(PartyID, PartyName, PartyDate, Nature_ofParty,
PartyAddress, CustID)
VALUES
('1','Francis 28th

Birthday','11/01/2014','Bday',
'Zarraga','2'),
('2','Death Anniversary','12/24/2014','Burial',
'Leganes','3'),
('3','Jing Jing Bday','1/1/2015','Bday',
'Jaro','2');

INSERT INTO Food
(FoodID, FoodName, FoodType)
VALUES
('1','Solar Dried Fish','Snack'),
('2','KBL','Dish'),
('3','Menudo','Dish');

SELECT * FROM Food;

INSERT INTO Menu
(MenuID, FoodID, PartyID, No_Servings)
VALUES
('1','1','1','200'),
('2','3','2','150'),
('3','3','3','50');

SELECT * FROM Menu;

SELECT Lastname, Firstname, Middlename FROM

Customer;

SELECT FoodName, FoodType FROM Food;

SELECT Food.FoodName, Food.FoodType,

Menu.No_Servings
FROM Party
INNER JOIN Menu
ON Party.PartyID = Menu.PartyID
INNER JOIN Food
ON Menu.FoodID = Food.FoodID
WHERE Party.PartyName = 'Jing Jing Bday'



SELECT Party.PartyName, Party.PartyAddress
FROM Customer
INNER JOIN Party
ON Customer.CustID = Party.CustID
WHERE Customer.Lastname = 'Astronomia';

SELECT Customer.Lastname FROM Food
INNER JOIN Menu
ON Food.FoodID = Menu.FoodID
INNER JOIN Party
ON Menu.PartyID = Party.PartyID
INNER JOIN Customer
ON Party.CustID = Customer.CustID
WHERE Food.FoodName = 'Menudo';

SQL Basics

CREATE TABLE Students
(
StudentID int PRIMARY KEY NOT NULL,
StudName varchar(50) NOT NULL
);

CREATE TABLE Books
(
BookID int PRIMARY KEY,
Title varchar(50) NOT NULL,
Author varchar(50)
);

CREATE TABLE Borrow
(
BorrowID int PRIMARY KEY,
StudentID int NOT NULL,
BookID int NOT NULL
);

INSERT INTO Students
(StudentID, StudName)
VALUES
('1','Alquisada');

INSERT INTO Students
(StudentID, StudName)
VALUES
('2','Ardena'),
('3','Suner'),
('4','Eusuya'),
('5', 'Arca');

SELECT * FROM Students;

SELECT StudName FROM Students;

INSERT INTO Books
(BookID, Title, Author)
VALUES
('1','Harry Potter','JK Rowling');

INSERT INTO Books
(BookID, Title, Author)
VALUES
('2','Percy Jackson','Rick Riordan');

SELECT * FROM Books;

INSERT INTO Borrow
(BorrowID, StudentID, BookID)
VALUES
('1','1','1'),
('2','1','2'),
('3','2','1');


SELECT * FROM Borrow;

SELECT
Students.StudName, Books.Title, Books.Author
FROM Students
INNER JOIN Borrow
ON Students.StudentID = Borrow.StudentID
INNER JOIN Books
ON Borrow.BookID = Books.BookID;

SELECT
Students.StudName, Books.Title, Books.Author
FROM Students
INNER JOIN Borrow
ON Students.StudentID = Borrow.StudentID
INNER JOIN Books
ON Borrow.BookID = Books.BookID
WHERE Students.StudentID = '2'
;
DROP TABLE Books;

ALTER TABLE Books
ALTER COLUMN Title varchar(50);

ALTER TABLE Books
ADD Book_Desc varchar(100);

ALTER TABLE Books
DROP COLUMN Book_Desc;

UPDATE Books
SET Title = 'Title 2', Author = 'dfgdfgfdg'
WHERE BookID = '2';

Library System: Notes and References

One Drive URL for the Library System:

http://1drv.ms/1dSRFWE

CPU Notes and References:

http://1drv.ms/1dSRLxt