1) Select pname,((sysDATE-dob)/365) as ages from programmer;
2) SELECT name FROM programmers WHERE gender = 'Female' AND
salary>3000 AND knows_cpp = 'No' AND knows_oracle = 'No' AND
knows_dbase = 'No';
3) SELECT institute_name, COUNT(*) as student_count FROM students
GROUP BY institute_name ORDER BY student_count DESC LIMIT 1;
4) SELECT language, COUNT(prof1) as prof1_count FROM programmer
GROUP BY language;
5) SELECT EXTRACT(YEAR FROM birthdate) as birth_year, COUNT(*) as
people_count FROM programmer GROUP BY EXTRACT(YEAR FROM
birthdate) ORDER BY birth_year;
6) SELECT * FROM programmer WHERE salary BETWEEN 2000 AND 4000;
7) Select programmer_name,title_software from software;
8) SELECT MAX(copies_sold) AS highest_copies_sold FROM packages;
9) SELECT institute_name, COUNT(student_id) AS student_count FROM
students GROUP BY institute_name;
10) SELECT EXTRACT(MONTH FROM birthdate) AS birth_month,
COUNT(*) AS people_count
11) FROM programmers GROUP BY EXTRACT(MONTH FROM birthdate)
ORDER BY birth_month;
12) SELECT PROF1 FROM PROGRAMMER WHERE SEX = 'M' UNION
DISTINCT SELECT PROF2 FROM PROGRAMMER WHERE SEX = 'M';
13) SELECT MIN(course_fee) AS lowest_course_fee FROM studies;
14) SELECT course_name, COUNT(*) AS num_students FROM studies
GROUP BY course_name ORDER BY num_students DESC LIMIT 1;
15) SELECT institute_name, COUNT(DISTINCT course_name) AS
num_courses, AVG(course_fee) AS avg_cost_per_course FROM studies
GROUP BY institute_name;
16) SELECT institute_name, COUNT(DISTINCT person_id) AS num_people
FROM studies GROUP BY institute_name;
17) SELECT * FROM software WHERE developer = 'Ramesh';
18) SELECT COUNT(*) FROM PROGRAMMER WHERE SALARY BETWEEN
2000 AND 4000;
19) SELECT * FROM SOFTWARE WHERE software_language = 'NET'
ORDER BY software_cost DESC LIMIT 1;
20) SELECT s.name AS software_name, s.cost FROM programmer p JOIN
software s ON p.programmer_id = s.developer_id WHERE s.language =
'NET' ORDER BY s.cost DESC LIMIT 1;
21) SELECT p.name AS programmer_name, SUM(s.sales_cost) AS
total_sales_cost FROM programmer p JOIN software s ON p.programmer_id
= s.developer_id GROUP BY p.name;
22) SELECT prof1, COUNT(*) AS language_count FROM programmer
GROUP BY prof1 ORDER BY language_count DESC LIMIT 1;
23) SELECT MAX(s.cost) AS costliest_software_price FROM software s
JOIN programmer p ON s.programmer_id = p.programmer_id WHERE
s.language = 'BASIC';
24) SELECT * FROM programmer WHERE prof1 NOT IN ('Fortran', 'Cobol',
'.NET') AND prof2 NOT IN ('Fortran', 'Cobol', '.NET') AND prof3 NOT IN
('Fortran', 'Cobol', '.NET');
25) SELECT name FROM programmer WHERE LENGTH(name) <= 5;
26) SELECT p.name AS programmer_name, COUNT(s.software_id) AS
num_softwares_sold
27) FROM programmer p LEFT JOIN software s ON p.programmer_id =
s.programmer_id GROUP BY p.name;
28) SELECT DATEDIFF(CURRENT_DATE(), DATE_OF_BIRTH) AS
age_of_oldest_male_programmer FROM programmer WHERE gender =
'Male' ORDER BY DATE_OF_BIRTH ASC LIMIT 1;
29) CREATE TABLE programmer (programmer_id INT PRIMARY KEY,name
VARCHAR(50), gender CHAR(1), date_of_birth DATE);
30) CREATE TABLE software (software_id INT PRIMARY
KEY,name,VARCHAR(100),programmer_id INT, FOREIGN KEY
(programmer_id) REFERENCES programmer(programmer_id);
31) Select pname from studies where course_name=’DAP’;
32) SELECT COUNT(*) AS num_female_programmer FROM programmer
WHERE gender = 'Female' AND experience > 2 AND prof1=’cobol’ OR
prof2=’cobol’;
33) SELECT platform_language , COUNT(*) AS num_softwares FROM
software WHERE platform_language NOT IN ('C', 'C++') GROUP BY
platform_language;
34) SELECT COUNT(*) AS num_programmers FROM programmer WHERE
".NET" NOT IN (prof1, prof2, prof3) AND "C" NOT IN (prof1, prof2, prof3);
35) SELECT * FROM software WHERE sales > 2000;
36) SELECT name, gender FROM programmer;
37) SELECT language, name, MAX(salary) AS highest_salary FROM
programmer GROUP BY language;
38) SELECT language, SUM(cost) AS total_cost FROM software GROUP
BY language;
39) SELECT name, (YEAR(CURRENT_DATE) - YEAR(start_date)) AS
experience_years FROM programmer ORDER BY experience_years DESC;
