restaurant.sqbpro

<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="C:/Users/Phone Thiri Yadana/Documents/02.Studies/SQL/exercises/MySQL Projects/SQL Code Challenges - Lynda/In the Restaurant/restaurant.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="0"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="1272"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="4,12:mainOrdersDishes"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="Customers" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="84"/><column index="2" value="85"/><column index="3" value="80"/><column index="4" value="212"/><column index="5" value="177"/><column index="6" value="105"/><column index="7" value="44"/><column index="8" value="97"/><column index="9" value="73"/><column index="10" value="88"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Dishes" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="49"/><column index="2" value="169"/><column index="3" value="300"/><column index="4" value="40"/><column index="5" value="61"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Orders" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort><column index="1" mode="1"/></sort><column_widths><column index="1" value="59"/><column index="2" value="84"/><column index="3" value="129"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="OrdersDishes" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort><column index="1" mode="1"/></sort><column_widths><column index="1" value="108"/><column index="2" value="59"/><column index="3" value="49"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="Restaurant_Challenges.sql">
/*********************** Time to Celebrate Restaurant Anniversary ***************************************/

/**************************************************************/
/* Create invitations for a party                             */
/**************************************************************/
SELECT FirstName, LastName, Email
FROM Customers
ORDER BY LastName;


/**************************************************************/
/* Create a table to store information                        */
/**************************************************************/
CREATE TABLE PartyAttendees(
CustomerID INTEGER,
PartySize INTEGER,
FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID)
);


/**************************************************************/
/* Print a menu							                      */
/**************************************************************/
SELECT * FROM Dishes
LIMIT 2;

SELECT DISTINCT(Type)
FROM Dishes;

/*All items sorted by price, low to high*/
SELECT *
FROM Dishes
ORDER BY Price;

/*Appetiers and beverages, by type*/
SELECT *
FROM Dishes
WHERE Type IN (&quot;Beverage&quot;, &quot;Appetizer&quot;)
ORDER BY Type;

/*All items except beverages, by type*/
SELECT *
FROM Dishes
WHERE Type NOT IN (&quot;Beverage&quot;)
ORDER BY Type;


/**************************************************************/
/* Sign a cutomer up for your loyalty program                 */
/**************************************************************/
INSERT INTO Customers(FirstName, LastName, Email, Address, City, State, Phone, Birthday)
VALUES ('Vivian', 'Fong', 'vf@email.com', '123 forest street', 'Singapore', 'ABC', '123-456', '27-08-2000');

SELECT * FROM Customers
ORDER BY CustomerID DESC;


/**************************************************************/
/* Update Customer Personal Information                       */
/**************************************************************/
SELECT CustomerID, FirstName, LastName, Address, City, State
FROM Customers WHERE FirstName = &quot;Taylor&quot; AND LastName = &quot;Jenkins&quot;;

UPDATE Customers
SET Address = &quot;74 Pine St&quot;, City = &quot;New York&quot;, State = &quot;NY&quot;
WHERE CustomerID = 26;

SELECT * FROM Customers
WHERE CustomerID = 26;


/**************************************************************/
/* Remove Customer Record                                     */
/**************************************************************/
SELECT CustomerID, FirstName, LastName, Email, Phone
FROM Customers WHERE FirstName = &quot;Taylor&quot; AND LastName = &quot;Jenkins&quot;;

DELETE
FROM Customers
WHERE CustomerID = 4;


/**************************************************************/
/* Log Customer Responses                                     */
/**************************************************************/
INSERT INTO PartyAttendees(CustomerID, PartySize)
VALUES( 
	(SELECT CustomerID FROM Customers WHERE Email LIKE 'atapley2j@kinetecoinc.com')
	,3
);

SELECT * FROM PartyAttendees;


/**************************************************************/
/* Look up Reservations                                       */
/**************************************************************/
SELECT r.*, c.FirstName, c.LastName, c.Email, c.Phone
FROM Reservations r
JOIN Customers c
ON r.CustomerID = c.CustomerID
WHERE c.LastName LIKE 'Ste%'
ORDER BY r.Date DESC;


/**************************************************************/
/* Take a Reservation                                         */
/**************************************************************/
SELECT * FROM Customers 
WHERE Email = 'smac@rouxacademy.com';

INSERT INTO Customers(FirstName, LastName, Email, Phone)
VALUES('Same', 'McAdams', 'smac@rouxacademy.com', '(555)555-1212)');

INSERT INTO Reservations(CustomerID, Date, PartySize)
VALUES('102','2020-07-14 18:00:00','5');

SELECT * FROM Reservations 
WHERE CustomerID = 102;


/**************************************************************/
/* Take a Delivery Order                                      */
/**************************************************************/
/* check cusotmer exist or not */
SELECT * FROM Customers
WHERE FirstName = 'Loretta' AND LastName = 'Hundey';

/* check dishes */
SELECT * FROM Dishes;

/* create order */
INSERT INTO Orders(CustomerID, OrderDate)
VALUES(
	(SELECT CustomerID FROM Customers WHERE FirstName = 'Loretta' AND LastName = 'Hundey'),
	'27-08-2020 20:00:00'
);

/* check the latest order of that customer */
SELECT OrderID FROM Orders WHERE CustomerID IN 
		(SELECT CustomerID FROM Customers WHERE FirstName = 'Loretta' AND LastName = 'Hundey')
ORDER BY OrderID DESC
LIMIT 1;


/* Create order dishes */
INSERT INTO OrdersDishes(OrderID, DishID)
VALUES(
	(SELECT OrderID FROM Orders WHERE CustomerID IN 
		(SELECT CustomerID FROM Customers WHERE FirstName = 'Loretta' AND LastName = 'Hundey')
	ORDER BY OrderID DESC
	LIMIT 1),
	(SELECT DishID FROM Dishes WHERE Name LIKE 'House Salad' )
);

INSERT INTO OrdersDishes(OrderID, DishID)
VALUES(
	(SELECT OrderID FROM Orders WHERE CustomerID IN 
		(SELECT CustomerID FROM Customers WHERE FirstName = 'Loretta' AND LastName = 'Hundey')
	ORDER BY OrderID DESC
	LIMIT 1),
	(SELECT DishID FROM Dishes WHERE Name LIKE 'Mini Cheeseburgers' )
);

INSERT INTO OrdersDishes(OrderID, DishID)
VALUES(
	(SELECT OrderID FROM Orders WHERE CustomerID IN 
		(SELECT CustomerID FROM Customers WHERE FirstName = 'Loretta' AND LastName = 'Hundey')
	ORDER BY OrderID DESC
	LIMIT 1),
	(SELECT DishID FROM Dishes WHERE Name LIKE 'Tropical Blue Smoothie' )
);

/* Calculate Total Cost */
SELECT d.Name, SUM(d.Price)
FROM OrdersDishes od
LEFT JOIN Dishes d 
ON od.DishID = d.DishID
WHERE od.OrderID == 1001;


/**************************************************************/
/* Track Customer Favourite                                   */
/**************************************************************/

SELECT *
FROM Customers
WHERE FirstName = 'Cleo' AND LastName = 'Goldwater';


UPDATE Customers
SET FavoriteDish = (SELECT DishID FROM Dishes WHERE Name LIKE 'Quinoa Salmon Salad')
WHERE FirstName = 'Cleo' AND LastName = 'Goldwater';


SELECT *
FROM Customers c
JOIN Dishes d
ON c.FavoriteDish = d.DishID
WHERE FirstName = 'Cleo' AND LastName = 'Goldwater';


/**************************************************************/
/* Report of Top 5 Customers                                  */
/**************************************************************/
SELECT FirstName, LastName, Email, COUNT(OrderID) AS NumberOfOrders
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
ORDER BY 4 DESC
LIMIT 5;
</sql><current_tab id="0"/></tab_sql></sqlb_project>