Scribd
Upload
9 views10 pages

MySQL Assignment Solved

The document provides solutions to MySQL assignments, including SQL queries and results related to transactions and trainers. It covers various tasks such as displaying transaction details, calculating averages, and filtering data based on specific criteria. Additionally, it includes screenshots of base tables for better understanding of the queries executed.

Uploaded by

flixpog
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views10 pages

MySQL Assignment Solved

The document provides solutions to MySQL assignments, including SQL queries and results related to transactions and trainers. It covers various tasks such as displaying transaction details, calculating averages, and filtering data based on specific criteria. Additionally, it includes screenshots of base tables for better understanding of the queries executed.

Uploaded by

flixpog
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MySQL – Assignment Solutions

Includes SQL queries, results, and realistic table screenshots.

Base Tables (Screenshots)


Assignment 1
(i) To display details of all transactions of TYPE 'Credit' from table TRANSACTION.
(ii) To display the CNO and AMOUNT of all transactions done in the month of September 2017.

(iii) To display the last date of transaction (DOT) for the customer having CNO as 103.
(iv) To display all CNO, CNAME and DOT (date of transaction) whose transactions are >= 2000.

(v) SELECT COUNT(*), AVG(AMOUNT) FROM TRANSACTION WHERE DOT >= '2017-06-01';
(vi) SELECT CNO, COUNT(*), MAX(AMOUNT) FROM TRANSACTION GROUP BY CNO HAVING COUNT(*)>1;

(vii) SELECT CNO, CNAME FROM CUSTOMERS WHERE ADDRESS NOT IN ('DELHI','BANGALORE');
(viii) SELECT DISTINCT CNO FROM TRANSACTION;

Assignment 2
(a) Display the Trainer Name, City and Salary in descending order of their hire date.
(b) Display the TNAME and CITY of Trainers who joined in December 2001.

(c) Display TNAME, HIREDATE, CNAME, STARTDATE from TRAINER and COURSE whose FEES <= 10000.
(d) Display number of trainers from each city.

(e) SELECT TID, TNAME FROM TRAINER WHERE CITY NOT IN ('DELHI','MUMBAI');
(f) SELECT DISTINCT TID FROM COURSE;

(g) SELECT TID, COUNT(*), MIN(FEES) FROM COURSE GROUP BY TID HAVING COUNT(*)>1;
(h) SELECT COUNT(*), SUM(FEES) FROM COURSE WHERE STARTDATE < '2018-09-15';

You might also like