SQL – Practice

Create the Below Tables.

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 SDBT Pgdca 4500
Altaf SSIL Dca 7200
Jagadesh SSIL Dca 3500
Juliana BITS Dca 22000
Kamala SAKTHIDBTECH Dcp 5000
Mary SDBT Pgdca 4500
Nelson SAKTHIDBTECH Dap 6200
Partick SAKTHIDBTECH Dcap 5200
Qadir APPLE Hdcp 14000
Ramesh SDBT Pgdca 4500
Rebecca SSS Dca&p 11000
Remitha MICROSOFT Dcs 6000
Revathi SDBT Dap 5000
Vijaya MICROSOFT 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 SDBT.
    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 SAKTHIDBTECH.
    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 PASCAL & 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.
    47. 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.
    30. 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?
    38. 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 SAKTHIDBTECH.
    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 SDBT.
    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 MICROSOFT.
    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.
    30. List all the details of programmers who has done a course at SSIL.

NEXT Exercise

Table Employee

Sno Name Salary Comm
1 KAMARAJ 1000
2 CHIDHAMBARAM 1000
3 SAKTHI PRIAN 2000
4 MOHANA KRISHNAN 2000 2000
5 MAHESH 3000
6 SASHI KUMAR 3000
7 PADMANABHAN A 3000
9 MAHESH 2000
10 SAI CHARAN 30000

 

  1. Find the sum of salary and comm (Salary+Comm)
  2. List the words that have 2 ‘A’s.
  3. List Name and 3 characters after 2rd ‘A’ of the words.(ex in SAI CHARAN, get ‘RAN ‘, in PADMANABHAN A, get ‘NAB’
  4.  List the name and salary (if , salary = 1000, then print ‘one thousand, 2000 then two thousand, 3000 then three thousand, else ‘NO SALARY’)

 

SQL Interview Questions

  1. what is the key word used with Update?
  2. what is the key words to insert data in table?
  3. What is the query used to insert multiply column is single query?
  4. rollback can be used after truncate?
  5. Difference between delete and truncate?
  6. command used to save or undo all changes.
  7. How to fetch all tables?
  8. How to modify datatype query?
  9. what will happen after using delete?
  10. how to drop a column in a table.
  11. difference between truncate, delete and drop related questions
  12. How will you get the source code of a Table/INDEX/VIEW/SYNONYM .Hint(DBMS package)
  13. How will you check the size of a table / index?
  14. For which column you will create a index.
  15. What are the types of index?
  16. How to drop a record with child record exists
  17. Is it possible create two tables with same anem “sdbt” and “SDBT” (upper and Lower case)
  18. Is DDL Autocommit?
  19. What is the dml statement which is used for Insert as well as update.
  20. How will find your age using sysdate and your DOB using dual.
  21. How will drop a disable a primary key from a table.
  22. How will you truncate all tables from a given schema?
  23. How to check the status of a constraint?