RDBMS using MySQL - Practice Question

 

To paste it you need to install this extension click here to download extension 

Web Technology Practice: Click Here

Python Practice: Click Here

Java Practice: Click Here

AWS Practice: Click Here

Create Table 1

create table Customer_Master(Cust_Id varchar(10), C_first_name varchar(30), C_middle_name varchar(30), C_last_name varchar(30), Gender varchar(10), Street varchar(30), City varchar(30), Phoneno varchar(20), Email varchar(30), primary key(Cust_Id));

Create Table 2

CREATE TABLE Venue_Master(Venue_Id varchar(10), Event_Id varchar(10), Venue_name varchar(30), Location varchar(50), Venue_Cost int, Food varchar(10),Capacity int, Wifi varchar(10), Description varchar(50), primary key(Venue_Id), foreign key(Event_Id) references Event_Master(Event_Id));

Alter 1

ALTER TABLE Venue_Master MODIFY COLUMN Wifi varchar(10) NOT NULL;

Alter 2

ALTER table Booking_Master add foreign key(Enquiry_Id) references Enquiry_Master(Enquiry_Id);

Truncate 1

TRUNCATE TABLE Booking_Master;

Drop 1

DROP TABLE Booking_Master;

Insert 1

INSERT INTO Booking_Master values('B101', 'EN1001', 40000, 'Cash'); INSERT INTO Booking_Master values('B102', 'EN1012', 750000, 'Online'); INSERT INTO Booking_Master values('B103', 'EN1016', 16000, 'Cheque'); INSERT INTO Booking_Master values('B104', 'EN1007', 10000, 'Cash');

Insert 2

INSERT INTO Customer_Master (Cust_Id, C_first_name, Gender, City,Phoneno) VALUES('C001', 'Liam', 'Male', 'New York', '9235234530'), ('C002', 'Olivia', 'Female', 'Boston', '9235234531'), ('C003', 'James', 'Male', 'Austin', '9235234532'), ('C004', 'Henry', 'Male', 'San Antonio', '9235234533'), ('C005', 'Noah', 'Male', 'Chicago', '9235234534');

Update 1

UPDATE Customer_Master SET Street='No.21,Abbey Road', City='Denver' WHERE C_first_name='Emma';

Update 2

UPDATE Venue_Master SET Capacity=Capacity+(Capacity/10) WHERE Location='San Antonio';

Delete 1

DELETE FROM Booking_Master;

Delete 2

DELETE FROM Enquiry_Master WHERE Venue_id='V04' and Status='Available';

Customer Details

SELECT C_first_name, Gender, Phoneno FROM Customer_Master ORDER BY C_first_name;

Customers Details Based On Name

SELECT C_first_name, C_last_name, Street, City FROM Customer_Master WHERE C_first_name LIKE '_m%' ORDER BY C_first_name;

Booking Details

SELECT * FROM Booking_Master;

Venue Details With Wifi Facility

SELECT Venue_Id, Venue_name, Location, Venue_cost FROM Venue_Master WHERE Wifi='Yes' AND (Location!='New York' AND Location!='Austin') ORDER BY Venue_cost;

Customers Details Based On Last Name And Middle Name

SELECT C_first_name, Phoneno, Gender FROM Customer_Master WHERE (C_last_name IS NULL OR C_middle_name IS NULL) ORDER BY Phoneno;

Booking Details Based On Payment Mode

SELECT Booking_Id, Enquiry_Id, Total_amount FROM Booking_Master WHERE (Mode_of_Pay='Cash' OR Mode_of_Pay='Credit') ORDER BY Enquiry_Id ASC;

Venue Details Based On Capacity

SELECT Venue_Id, Venue_name, Venue_cost, Location FROM Venue_Master WHERE Capacity BETWEEN 1000 AND 20000 ORDER BY Venue_name;

Customer Contact Info

SELECT Cust_Id, C_first_name, COALESCE(City, Phoneno, Email) as CONTACT_INFO FROM Customer_Master ORDER BY CONTACT_INFO;

Venue Details

SELECT Venue_name, Venue_cost, CASE WHEN Venue_cost BETWEEN 50000 AND 200000 THEN 'Medium Cost' WHEN Venue_cost<50000 THEN 'Low Cost' WHEN Venue_cost>200000 THEN 'High Cost' END AS 'cost_category' FROM Venue_Master ORDER BY Capacity DESC;

Customer Info

SELECT concat(C_first_name, ' lives in ', city, ' and ', CASE WHEN Gender='Male' THEN 'his' else 'her' END, ' phone number is ', Phoneno) as customer_info from Customer_Master ORDER BY C_first_name DESC;

Finding Position

SELECT C_first_name, INSTR(C_first_name, 'm') AS position FROM Customer_Master WHERE City='Los Angeles' OR City='Boston' ORDER BY position DESC, C_first_name ASC;

Customer Password

SELECT C_first_name as user_name, concat(substr(C_first_name,1,3), substr(Phoneno, 5,2)) as password from Customer_Master WHERE Email like '%@gmail.com' ORDER BY user_name;

Month Name For The Events

SELECT Enquiry_Id, Cust_Id, CONCAT(MONTHNAME(Start_Date), ',', YEAR(Start_Date)) as Event_date FROM Enquiry_Master WHERE Start_Date>'2015-12-31' ORDER BY Event_date, Cust_Id;

Number Of Days

SELECT Enquiry_Id, Cust_Id, Venue_ID, MONTHNAME(Start_Date) as month_name, DATEDIFF(End_Date, Start_Date)+1 as no_of_days FROM Enquiry_Master WHERE MONTHNAME(Start_Date)='December' ORDER BY no_of_days DESC;

Weekend Events

SELECT Enquiry_Id, Cust_Id FROM Enquiry_Master WHERE DAYOFWEEK(Start_Date) IN (1,7) AND DAYOFWEEK(End_Date) IN (1,7) ORDER BY Cust_Id;

Maximum Seating Capacity Of Events

SELECT Event_Id, max(Capacity) as maximum_seating_capacity FROM Venue_Master WHERE Wifi='Yes' GROUP BY Event_Id ORDER BY maximum_seating_capacity DESC;

Min Amount In Cash Or Online Method

SELECT Mode_of_Pay, MIN(Total_amount) as MINIMUM_TOTAL_AMOUNT FROM Booking_Master WHERE Mode_of_Pay='Cash' or Mode_of_Pay='Online' GROUP BY Mode_of_Pay ORDER BY MINIMUM_TOTAL_AMOUNT;

Customer Count

SELECT COUNT(*) AS ENQUIRY_COUNT FROM Enquiry_Master GROUP BY Cust_Id HAVING COUNT(*)>1 ORDER BY ENQUIRY_COUNT DESC;

Average Cost For Location

SELECT Location, Round(AVG(Venue_Cost),2) as Avg_cost FROM Venue_Master WHERE Capacity>1000 group by Location ORDER BY Location;

Customer Venue Dates

SELECT C_first_name, Venue_Id, Start_Date, End_Date FROM Customer_Master as C join Enquiry_Master as E on C.Cust_Id=E.Cust_Id ORDER BY C_first_name;

Customer Details Based On Payments

SELECT DISTINCT CM.Cust_Id, CM.C_first_name, CM.Phoneno, CM.Email FROM Customer_Master CM JOIN Enquiry_Master EM ON CM.Cust_Id = EM.Cust_Id JOIN Booking_Master BM ON BM.Enquiry_Id = EM.Enquiry_Id WHERE BM.Mode_of_Pay = 'Online' order by CM.Cust_Id;

Discount Code For Specific Customer

SELECT DISTINCT CM.C_first_name, CONCAT(LEFT(CM.C_first_name,3), LEFT(CM.Phoneno,3)) as Discount_Code FROM Customer_Master CM JOIN Enquiry_Master EM ON CM.Cust_Id=EM.Cust_Id JOIN Booking_Master BM ON EM.Enquiry_Id=BM.Enquiry_Id WHERE CM.City IN ('Los Angeles','Boston') AND BM.Total_amount > 50000 ORDER BY CM.C_first_name;

Number of Days

SELECT EM.Cust_Id, BM.Booking_Id, DATEDIFF(EM.End_Date, EM.Start_Date)+1 as Days FROM Enquiry_Master EM JOIN Booking_Master BM ON EM.Enquiry_Id=BM.Enquiry_Id WHERE DATEDIFF(EM.End_Date,EM.Start_Date)+1>3 ORDER BY EM.Cust_Id desc;

Number Of Customer For Venues

SELECT Venue_name, COUNT(EM.Enquiry_Id) as No_Of_Customers FROM Venue_Master VM LEFT JOIN Enquiry_Master EM ON VM.Venue_Id=EM.Venue_Id GROUP BY VM.Venue_name ORDER BY VM.Venue_name;

Customer Visited Same Venues

SELECT C1.C_first_name as C_First_Name, C2.C_first_name as C_First_Name, C1.City, EM1.Venue_Id FROM Customer_Master C1 JOIN Enquiry_Master EM1 ON C1.Cust_Id=EM1.Cust_Id JOIN Customer_Master C2 ON C1.City=C2.City AND C1.Cust_Id <> C2.Cust_Id JOIN Enquiry_Master EM2 ON C2.Cust_Id=EM2.Cust_Id AND EM1.Venue_Id = EM2.Venue_Id;

Customer Without Enquiry And Booking

SELECT CM.Cust_Id, CM.C_first_name, CM.City, CM.Phoneno FROM Customer_Master CM LEFT JOIN Enquiry_Master EM ON CM.Cust_Id = EM.Cust_Id WHERE EM.Enquiry_Id IS NULL order by CM.C_first_name;

Customer Total Booking Amount

SELECT CM.C_first_name, SUM(BM.Total_amount) AS total_booking_amount FROM Customer_Master CM JOIN Enquiry_Master EM ON CM.Cust_Id=EM.Cust_Id JOIN Booking_Master BM ON BM.Enquiry_Id=EM.Enquiry_Id GROUP BY CM.C_first_name HAVING SUM(BM.Total_amount)> (SELECT AVG(Total_amount) FROM (SELECT SUM(Total_amount) AS total_amount FROM Booking_Master GROUP BY Enquiry_Id) as subquery) ORDER BY CM.C_first_name;

Venue Details With Maximum And Minimum Cost

SELECT Venue_Id, Venue_name, Location FROM Venue_Master WHERE Venue_Cost=(SELECT MAX(Venue_Cost) FROM Venue_Master) OR Venue_Cost=(SELECT MIN(Venue_Cost) FROM Venue_Master) ORDER BY Venue_Cost;

Venue Details Based on Bookings

SELECT v.Venue_Id, v.Venue_name, v.Location, e.Category FROM Venue_Master v JOIN Event_Master e ON v.Event_Id=e.Event_Id JOIN Enquiry_Master enq ON enq.Venue_Id=v.Venue_Id JOIN Booking_Master b ON enq.Enquiry_Id=b.Enquiry_Id WHERE v.Venue_Id != 'V10' GROUP BY v.Venue_Id, v.Venue_name, v.Location, e.Category HAVING COUNT(b.Booking_Id) = (SELECT COUNT(b2.Booking_Id) FROM Booking_Master b2 JOIN Enquiry_Master enq2 ON b2.Enquiry_Id=enq2.Enquiry_Id JOIN Venue_Master v2 ON enq2.Venue_Id=v2.Venue_Id WHERE enq2.Venue_Id='V10') ORDER BY v.Venue_name DESC;

Highest Seating Capacity

SELECT e.Category, v.Venue_name, v.Capacity FROM Event_Master e JOIN Venue_Master v ON e.Event_Id=v.Event_Id WHERE v.Capacity=( SELECT MAX(v2.Capacity) FROM Venue_Master v2 JOIN Event_Master e2 ON v2.Event_Id=e2.Event_Id WHERE e2.Category=e.Category);

Create view

CREATE VIEW Boston_Customers AS ( SELECT Cust_Id, C_first_name, C_middle_name, C_last_name, Gender, Street, City, Phoneno, Email FROM Customer_Master WHERE City='Boston');

Customer Info

CREATE VIEW Customer_info AS SELECT cm.C_first_name, cm.Phoneno, cm.City, b.Total_amount FROM Customer_Master cm JOIN Enquiry_Master em ON cm.Cust_Id=em.Cust_Id JOIN Booking_Master b ON b.Enquiry_Id=em.Enquiry_Id WHERE b.Total_amount<60000 ORDER BY cm.C_first_name;

Create Index

CREATE INDEX stuindex on student (student_name);