Scribd
Upload
17 views2 pages

Practical SQL Answers

The document contains SQL commands for creating and inserting data into two tables: FACULTY and COURSES. It includes various SELECT, UPDATE, and JOIN queries to retrieve and manipulate data based on specific conditions. Additionally, it presents further queries related to experience and records from other tables like COMPANY and CUSTOMER.

Uploaded by

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

Practical SQL Answers

The document contains SQL commands for creating and inserting data into two tables: FACULTY and COURSES. It includes various SELECT, UPDATE, and JOIN queries to retrieve and manipulate data based on specific conditions. Additionally, it presents further queries related to experience and records from other tables like COMPANY and CUSTOMER.

Uploaded by

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

Question:1

Creating and inserting table(Faculty)


CREATE TABLE FACULTY ( F_ID INT PRIMARY KEY, FNAME VARCHAR(50), LNAME VARCHAR(50),
HIRE_DATE DATE, SALARY INT );

INSERT INTO FACULTY (F_ID, FNAME, LNAME, HIRE_DATE, SALARY) VALUES (102, 'Amit', 'Mishra', '1998-
10-12', 12000), (103, 'Nitin', 'Vyas', '1994-12-24', 8000), (104, 'Rakshit', 'Soni', '2001-05-18', 14000), (105,
'Rashmi', 'Malhotra', '2004-09-11', 11000), (106, 'Sulekha', 'Srivastava', '2006-06-05', 10000);

Creating and inserting table(Course)

CREATE TABLE COURSES ( C_ID CHAR(3) PRIMARY KEY, F_ID INT, CNAME VARCHAR(50), FEES INT,
FOREIGN KEY (F_ID) REFERENCES FACULTY(F_ID) );

INSERT INTO COURSES (C_ID, F_ID, CNAME, FEES) VALUES ('C21', 102, 'Grid Computing', 40000), ('C22',
106, 'System Design', 16000), ('C23', 104, 'Computer Security', 8000), ('C24', 106, 'Human Biology',
15000), ('C25', 102, 'Computer Network', 20000), ('C26', 105, 'Visual Basic', 6000);

1. SELECT * FROM FACULTY WHERE SALARY > 12000;


2. SELECT * FROM COURSES WHERE FEES BETWEEN 15000 AND 50000;
3. SELECT [Link], COURSES.COURSE_NAME FROM FACULTY JOIN COURSES ON
FACULTY.COURSE_ID = COURSES.COURSE_ID;
4. UPDATE COURSES SET FEES = FEES + 500 WHERE COURSE_NAME = 'System Design';
5. SELECT * FROM FACULTY FAC,COURSES COUR WHERE FAC.F_ID = COUR.F_ID AND [Link] =
'SULEKHA' ORDER BY CNAME DESC
6. SELECT * FROM COURSES WHERE FEES > 75000;
7. SELECT * FROM FACULTY WHERE LNAME LIKE 'S%' AND SALARY > 8000;
8. UPDATE FACULTY SET SALARY = SALARY + 500 WHERE HIRE_DATE < '2000-06-05';
9. SELECT * FROM FACULTY ORDER BY SALARY DESC;
10. SELECT * FROM FACULTY WHERE FNAME LIKE 'M%';
11. SELECT * FROM FACULTY WHERE YEAR(HIRE_DATE) = 2001;
12. UPDATE COURSES SET COURSE_NAME = 'Cloud Computing' WHERE COURSE_ID = 'C26';
13. SELECT * FROM FACULTY ORDER BY FNAME ASC;

Question: 2
1. SELECT FNAME, LNAME FROM FACULTY WHERE FNAME LIKE '%H%' OR LNAME LIKE '%H%';
2. SELECT SUBJECT, MIN(YEAR(CURDATE()) - YEAR(HIRE_DATE)) AS MIN_EXPERIENCE FROM
FACULTY GROUP BY SUBJECT;
3. SELECT DISTINCT CNAME AS SUBJECT FROM COURSES;
4. SELECT FNAME, LNAME, YEAR(CURDATE()) - YEAR(HIRE_DATE) AS EXPERIENCE FROM FACULTY
WHERE DESIGNATION = 'HOD';

Question:3
1. SELECT CITY, COUNT(*) AS TOTAL_RECORDS FROM COMPANY GROUP BY CITY;
2. SELECT MIN(PRICE) AS MIN_PRICE, MAX(PRICE) AS MAX_PRICE FROM CUSTOMER WHERE QTY >
10;
3. SELECT CITY FROM PRODUCT WHERE SUBSTRING(NAME, LENGTH(NAME)-1, 1) = 'r';
4. SELECT NAME, CITY, PRICE FROM COMPANY WHERE PRICE > 250;

You might also like