SQL LABS - BASIC PRACTICE

SQL BASIC PRACTICE 

Table Name: prog

Name	Null	Data Type	Description
NAME	NOT NULL	VARCHAR2(8)	Name
DOB	NOT NULL	DATE	Date of Birth
DOJ	NOT NULL	DATE	Date of Joining
SEX	NOT NULL	VARCHAR2(1)	Male or Female
PROF1		VARCHAR2(8)	Language 1
PROF2		VARCHAR2(8)	Language 2
SALARY	NOT NULL	NUMBER(4)	Salary

 

Table Name : soft

Name	Null	Data Type	Description
NAME	NOT NULL	VARCHAR2(8)	Name
TITLE	NOT NULL	VARCHAR2(20)	Developed Project Name
DEV_IN	NOT NULL	VARCHAR2(8)	Language Developed
SCOST		NUMBER(7,2)	Software Cost
DCOST		NUMBER(5)	Development Cost
SOLD		NUMBER(3)	No. Of Software Sold

 

Table Name : studies

Name	Null	Data Type	Description
NAME	NOT NULL	VARCHAR2(8)	Name
SPLACE	NOT NULL	VARCHAR2(9)	Studies Place
COURSE	NOT NULL	VARCHAR2(5)	Course Studies
CCOST	NOT NULL	NUMBER(5)	Course Cost

 

 

Data in Table : prog

NAME	DOB	DOJ	SEX	PROF1	PROF2	SALARY
Anand	21-Apr-66	21-Apr-92	M	Pascal	Basic	3200
Altaf	02-Jul-64	13-Nov-90	M	Clipper	Cobol	2800
Jagadesh	06-Oct-70	04-Oct-94	M	Oracle	Java	4100
Juliana	31-Jan-68	21-Apr-90	F	Cobol	Dbase	3000
Kamala	30-Oct-68	02-Jan-92	F	C	Dbase	2900
Mary	24-Jun-70	01-Feb-91	F	C++	Oracle	4500
Nelson	11-Sep-65	11-Oct-89	M	Cobol	Dbase	2500
Partick	19-Nov-65	21-Apr-90	M	Pascal	Clipper	2800
Qadir	31-Aug-65	21-Apr-93	M	Assembly	C	3000
Ramesh	03-May-67	28-Feb-91	M	Pascal	Dbase	3200
Rebecca	01-Jan-67	01-Dec-90	F	Basic	Cobol	2500
Remitha	19-Apr-70	20-Apr-93	F	C	Assembly	3600
Revathi	02-Dec-69	02-Jan-92	F	Pascal	Basic	3700
Vijaya	14-Dec-65	02-May-92	F	Foxpro	C	3500

 

Data in Table : studies

NAME	SPACE	COURSE	CCOST
Anand	Sabhari	Pgdca	4500
Altaf	Ccpp	Dca	7200
Jagadesh	Ssil	Dca	3500
Juliana	Bits	Dca	22000
Kamala	Pragathi	Dcp	5000
Mary	Sabhari	Pgdca	4500
Nelson	Pragathi	Dap	6200
Partick	Pragathi	Dcap	5200
Qadir	Apple	Hdcp	14000
Ramesh	Sabhari	Pgdca	4500
Rebecca	Brilliant	Dca&p	11000
Remitha	Bdps	Dcs	6000
Revathi	Sabhari	Dap	5000
Vijaya	Bdps	Dca	48000

 

Data in Table : Soft

Name	Title	Dev_in	Scost	Dcost	Sold
ANAND	PARASHUTES	BASIC	399.95	6000	43
ANAND	VIDEO THINKING PACK	PASCAL	7500.00	16000	9
JAGADESH	SERIAL LINK UTILITY	JAVA	800.00	7500	10
JAGADESH	SHARES MANAGEMENT	ORACLE	3000.00	12000	14
JULIANA	INVENTORY CONTROL	COBOL	3000.00	3500	0
KAMALA	PARCEL PACKAGE	DBASE	9000.00	20000	7
MARY	FINANCIAL ACC S/W	ORACLE	18000.00	85000	4
MARY	CODE GENERATOR	C	4500.00	20000	23
MARY	READ ME	C++	300.00	1200	84
PARTICK	GRAPHIC EDITOR	PASCAL	750.00	5000	11
QADIR	BOMBS AWAY	ASSEMBLY	499.95	530	114
QADIR	VACCINES	C	1900.00	3400	21
RAMESH	HOTEL MANAGEMENT	DBASE	12000.00	35000	4
RAMESH	DEAD LEE	PASCAL	99.95	4500	73
REMITTA	PC UTILITIES	C	725.00	5000	51
REMITTA	TSR HELP PACKAGE	ASSEMBLY	2500.00	900	6
REVATHI	HOSPITAL MANAGEMENT	PASCAL	1100.00	75000	2
REVATHI	QUIZ MASTER	BASIC	3200.00	2100	15
VIJAYA	ISK EDITOR	C	900.00	700	6

Queries - I

1. Find out the SIMPLE COST AVERAGE FOR package developed in PASCAL.
2. Display the NAMES and AGES of all the programmers.
3. Display the NAMES of those who have done the DAP course.
4. When the HIGHEST number of copies sold by a package.
5. Display the NAMES and DATE OF BIRTH of all programmers born in JANUARY
6. Display the LOWEST course fee.
7. How many programmers have done the PGDCA course.
8. How much revenue has been earned through sale of packages developed in C.
9. Display the details of the SOFTWARE developed by RAMESH.
10. How many programmers studies at SABHARI.
11. Display the details of PACKAGES whose sales CROSSED the 20000 mark.
12. Find out the NUMBER OF COPIES, which should be sold in order to recover the DEVELOPMENT COST of each package.
13. Display the detail of packages for WHICH development cost has been recorded.
14. What is the price of the costliest software developed in BASIC?  How many packages were developed in DBASE.
15. How many programmers studies in PRAGATHI.
16. How many programmers paid 5000 to 10000 for their COURSE.  What is the AVERAGE course fee?
17. Display the DETAILS of programmers knowing C.
18. How many programmers know either COBOL or PASCAL.
19. How many programmers DON’T know PASCA: & C.
20. How old is the OLDEST male programmer.
21. What is the AVERAGE age of female programmers.
22. CALCULATE the experience in years for each programmers and display along with the names, in DESCENDING order.
23. How many female programmers are there.
24. What are the languages known by the male programmers.
25. What is the Average salary?
26. How many people draw 2000 to 4000.
27. Display the details of those WHO DON’T know CLIPPER, COBOL or PASCAL.
28. How many FEMALE programmers knowing C are above 24 years of age.
29. Who are the programmers who will be celebrating their Birthdays within a WEEK?
30. Display the details of those with LESS than a year EXPERIENCE.
31. Display the details of those who will be COMPLETING 2 years of service this YEAR.
32. CALCULATE the amount TO BE recovered for those package WHOSE development cost has not yet been recovered.
33. List the packages, which have not been sold so far.
34. Find out the COST of the software developed by MARY.
35. Display the institute names FROM the Studies table WITH OUT DUPLICATES.
36. How many different courses are mentioned in the studies table.
37. Display the names of the programmers whose names contain 2 OCCURENCES of the letter ’A’.
38. Display the names of the programmers WHOSE names contain UPTO 5 characters.
39. How many female programmers knowing COBOL have more than 2 years Experience.
40. What is the LENGTH of the shortest name in the programmer table?
41. What is the AVERAGE development cost of a package developed in COBOL?
42. Display the name, sex, DOB (DD/MM/YY format), DOJ (DD/MM/YY format) for all the programmers WITHOUT using conversion function.
43. What is the amount paid in salaries of the males programmers WHO DON’T know COBOL?
44. Who are the programmers WHO WERE BORN on the LAST DAY of the MONTH.
45. Display the title, SCOST, DCOST and DIFFERENCE between SCOST and DCOST in DESCENDING or of DIFFERENCE.
46. Display the names of the packages WHOSE names contain MORE THAN 1 word.
Display the name, job of THOSE MONTH of BIRTH and MONTH of JOINING are the SAME.

 

Queries – II

 

1. Display THE NUMBER OF packages developed IN EACH language.
2. Display THE NUMBER OF packages developed by EACH person.
3. Display THE NUMBER OF male and female programmers.
4. Display THE COSTLIEST package and HIGHEST SEELING.
5. Display THE NUMBER Of people BORN in EACH YEAR.
6. Display THE NUMBER OF people JOINED in EACH YEAR.
7. Display THE NUMBER OF people BORN in EACH YEAR.
8. Display THE NUMBER OF people JOINED in EACH MONTH.
9. Displays the language wise COUNT of prof1.
10. Displays the language wise COUNT of prof2.
11. Display THE NUMBER OF people in EACH salary group.
12. Display THE NUMBER OF people WHO studies in EACH institute.
13. Display THE NUMBER OF people WHO studies in EACH course.
14. Display the TOTAL development COST of the packages developed in EACH language.
15. Display the selling cost of the packages developed in EACH language.
16. Display the cost of the package developed EACH programmer.
17. Display the sales values of the packages developed by EACH programmer.
18. Display THE NUMBER of packages sold by EACH programmer.
19. Display the sales COST of the packages developed by EACH programmer language wise.
20. Display EACH programmer’s name, costliest package & CHEAPEST packages developed by him/her.
21. Display EACH language name with AVERAGE development cost, AVERAGE selling cost and AVERAGE price per copy.
22. Display EACH institute name with number of courses, AVERAGE cost per course.
23. Display EACH institute name with NUMBER of students.
24. Display the names of male and female programmers.
25. Display the programmer’s name and their packages.
26. Display the NUMBER of packages in EACH language EXCEPT C & C++.
27. Display the NUMBER of PACKAGES in EACH language for WHICH development COST is LESS than 1000.
28. Display the AVERAGE DIFFERENCE BETWEEN SCOST and DCOST for EACH language.
29. Display the TOTAL SCOST and DCOST and amount to be recovered for EACH programmer for those WHOSE DCOST has NOT YET BEEN recovered.
Display HIGHEST, LOWEST and AVERAGE salaries for THOSE earning MORE than 2000.

 

Queries - III

 

1. Who is the HIGHEST paid C programmer?
2. Who is the HIGHEST paid female COBOL programmer?
3. Display the names of the HIGHEST paid programmer for EACH language (prof1).
4. Who is the LEAST experienced programmer?
5. Who is the MOST experienced male programmer knowing PASCAL?
6. Which language is known by ONLY ONE programmer?
7. Who is that above programmer?
8. Who is the YOUNGEST programmer-knowing DBASE?
9. Which female programmer earning MORE than 3000/- DOESN’T know C, C++, Oracle or DBASE?
10. Which institute has the MOST NUMBER of students?
11. Which course has been done by the MOST of the students?
12. Display the name of the institute and course, WHICH has below AVERAGE course fee.
13. Which us the COSTLIEST course?
14. Which institute conducts the COSTLIEST course?
15. Which course has below AVERAGE number of students?
16. Which institute conducts the above course?
17. Display the names of the course WHOSE fees are within 1000/- (+ or -) of the AVERAGE fee.
18. Which package has the HIGHEST development cost?
19. Which package has LOWEST selling cost?
20. Which language was used to develop the package, which has the HIGHEST sales amount?
21. Which language was used to develop the package, which has the HIGHEST sales amount?
22. How many copies of the package that has the LEAST DIFFERENCE between development and selling cost, were sold?
23. Which is the costliest package developed in PASCAL?
24. Which language was used to develop the MOST NUMBER of packages?
25. Which programmer has developed the HIGHEST number of packages?
26. Who is the author of the COSTLIEST package?
27. Display the names of the package, WHICH have sold LESS THAN the AVERAGE number of copies.
28. Who are the authors of the package, WHICH have recovered MORE THAN double the development cost?
29. Display the programmer names and the CHEAPEST package developed by them in EACH language.
30. Display the language used by EACH programmer to develop the HIGHEST selling and the LOWEST selling package.
31. Who is the YOUNGEST male programmer born in 1965?
32. Who is the OLDEST female programmer WHO joined in 1992?
33. In WHICH year were the MOST NUMBER of programmers born.
34. In WHICH month did MOST NUMBER of the programmers join.
35. In WHICH language are MOST of the programmers proficient.
36. Who are the male programmers earning BELOW the AVERAGE salary of female programmers?
37. Who are the female programmers earning MORE than the HIGHEST paid male programmer?
Which language has been started as prof1 by the MOST of the Programmers?

 

Queries – IV

 

1. Which language has been started as prof1 by the MOST of the programmers.
2. Display the details of the software development by the male programmer earning MORE than 3000.
3. Display the details of the package developed in PASCAK by female programmers.
4. Display the details of those programmers WHO joined BEFORE 1990.
5. Display the details of the software developed in DBASE by female programmers of PRAGATHI.
6. Display the NUMBER of packages, NUMBER of copies sold and sales value of EACH programme, institute-wise.
7. Display the details of the software developed in DBASE by male programmers WHO belong to the institute in WHICH MOST NUMBER of programmer studied.
8. Display the details of the software developed by the male programmers born BEFORE 1995 and female programmers AFTER 1975.
9. Display the details of the software that was developed in the language that is NOT the programmer’s first proficiency.
10. Display the details of the software that was developed in the language WHICH is NEITHER the first NOT the second proficiency of the programmer.
11. Display the details of the software developed by the students of SABHARI.
12. Display the names of the programmers WHO HAVE NOT developed any package.
13. What is the total cost of the software developed by the programmers by APPLE?
14. Who are the programmers WHO JOINED on the same day?
15. Who are the programmers WHO HAVE the same prof2?]
16. Display the total sales value of software, institute wise.
17. In which institute did the person WHO developed the COSTILIEST package study.
18. Which language lasted in prof1 and prof2 HAS NOT BEEN used to develop any package?
19. How many does the person WHO developed the HIGHEST selling package earn and WHAT course did he/she undergo?
20. How many months will take for each programmer to recover the cost of the course underwent.
21. Which is the COSTILIEST package developed by a person with under 3 years experience?
22. What is the AVERAGE salary for those WHOSE software’s sales value is more than 50,000?
23. How many packages were developed by students WHO studies in the institute that charge the LOWEST course fee,
24. How many packages were developed by the person WHO developed the CHEAPEST package, WHERE did he/she study?
25. How many packages were developed by female programmers earning MORE than the HIGHEST paid male programmer.
26. How many packages were developed by MOST experienced programmer from BDPS.
27. List the programmers (from the software table) and the Institutes they studied, including those WHO DIDN’T develop any package.
28. List each prof1 with the number of programmers having that prof1 and the number of packages developed in that prof1.
29. List the programmer’s names (from the programmer table) and the no of packages EACH has developed.
List all the details of programmers who has done a course at SSIL.