SQL – Practice
Create the Below Tables.
Table Name: prog
|DOB||NOT NULL||DATE||Date of Birth|
|DOJ||NOT NULL||DATE||Date of Joining|
|SEX||NOT NULL||VARCHAR2(1)||Male or Female|
Table Name : soft
|TITLE||NOT NULL||VARCHAR2(20)||Developed Project Name|
|DEV_IN||NOT NULL||VARCHAR2(8)||Language Developed|
|SOLD||NUMBER(3)||No. Of Software Sold|
Table Name : studies
|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
Data in Table : studies
Data in Table : Soft
|ANAND||VIDEO THINKING PACK||PASCAL||7500.00||16000||9|
|JAGADESH||SERIAL LINK UTILITY||JAVA||800.00||7500||10|
|MARY||FINANCIAL ACC S/W||ORACLE||18000.00||85000||4|
|REMITTA||TSR HELP PACKAGE||ASSEMBLY||2500.00||900||6|
Queries – I
- Find out the SIMPLE COST AVERAGE FOR package developed in PASCAL.
- Display the NAMES and AGES of all the programmers.
- Display the NAMES of those who have done the DAP course.
- When the HIGHEST number of copies sold by a package.
- Display the NAMES and DATE OF BIRTH of all programmers born in JANUARY
- Display the LOWEST course fee.
- How many programmers have done the PGDCA course.
- How much revenue has been earned through sale of packages developed in C.
- Display the details of the SOFTWARE developed by RAMESH.
- How many programmers studies at SDBT.
- Display the details of PACKAGES whose sales CROSSED the 20000 mark.
- Find out the NUMBER OF COPIES, which should be sold in order to recover the DEVELOPMENT COST of each package.
- Display the detail of packages for WHICH development cost has been recorded.
- What is the price of the costliest software developed in BASIC? How many packages were developed in DBASE.
- How many programmers studies in SAKTHIDBTECH.
- How many programmers paid 5000 to 10000 for their COURSE. What is the AVERAGE course fee?
- Display the DETAILS of programmers knowing C.
- How many programmers know either COBOL or PASCAL.
- How many programmers DON’T know PASCAL & C.
- How old is the OLDEST male programmer.
- What is the AVERAGE age of female programmers.
- CALCULATE the experience in years for each programmers and display along with the names, in DESCENDING order.
- How many female programmers are there.
- What are the languages known by the male programmers.
- What is the Average salary?
- How many people draw 2000 to 4000.
- Display the details of those WHO DON’T know CLIPPER, COBOL or PASCAL.
- How many FEMALE programmers knowing C are above 24 years of age.
- Who are the programmers who will be celebrating their Birthdays within a WEEK?
- Display the details of those with LESS than a year EXPERIENCE.
- Display the details of those who will be COMPLETING 2 years of service this YEAR.
- CALCULATE the amount TO BE recovered for those package WHOSE development cost has not yet been recovered.
- List the packages, which have not been sold so far.
- Find out the COST of the software developed by MARY.
- Display the institute names FROM the Studies table WITH OUT DUPLICATES.
- How many different courses are mentioned in the studies table.
- Display the names of the programmers whose names contain 2 OCCURENCES of the letter ’A’.
- Display the names of the programmers WHOSE names contain UPTO 5 characters.
- How many female programmers knowing COBOL have more than 2 years Experience.
- What is the LENGTH of the shortest name in the programmer table?
- What is the AVERAGE development cost of a package developed in COBOL?
- Display the name, sex, DOB (DD/MM/YY format), DOJ (DD/MM/YY format) for all the programmers WITHOUT using conversion function.
- What is the amount paid in salaries of the males programmers WHO DON’T know COBOL?
- Who are the programmers WHO WERE BORN on the LAST DAY of the MONTH.
- Display the title, SCOST, DCOST and DIFFERENCE between SCOST and DCOST in DESCENDING or of DIFFERENCE.
- 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
- Display THE NUMBER OF packages developed IN EACH language.
- Display THE NUMBER OF packages developed by EACH person.
- Display THE NUMBER OF male and female programmers.
- Display THE COSTLIEST package and HIGHEST SEELING.
- Display THE NUMBER Of people BORN in EACH YEAR.
- Display THE NUMBER OF people JOINED in EACH YEAR.
- Display THE NUMBER OF people BORN in EACH YEAR.
- Display THE NUMBER OF people JOINED in EACH MONTH.
- Displays the language wise COUNT of prof1.
- Displays the language wise COUNT of prof2.
- Display THE NUMBER OF people in EACH salary group.
- Display THE NUMBER OF people WHO studies in EACH institute.
- Display THE NUMBER OF people WHO studies in EACH course.
- Display the TOTAL development COST of the packages developed in EACH language.
- Display the selling cost of the packages developed in EACH language.
- Display the cost of the package developed EACH programmer.
- Display the sales values of the packages developed by EACH programmer.
- Display THE NUMBER of packages sold by EACH programmer.
- Display the sales COST of the packages developed by EACH programmer language wise.
- Display EACH programmer’s name, costliest package & CHEAPEST packages developed by him/her.
- Display EACH language name with AVERAGE development cost, AVERAGE selling cost and AVERAGE price per copy.
- Display EACH institute name with number of courses, AVERAGE cost per course.
- Display EACH institute name with NUMBER of students.
- Display the names of male and female programmers.
- Display the programmer’s name and their packages.
- Display the NUMBER of packages in EACH language EXCEPT C & C++.
- Display the NUMBER of PACKAGES in EACH language for WHICH development COST is LESS than 1000.
- Display the AVERAGE DIFFERENCE BETWEEN SCOST and DCOST for EACH language.
- 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
- Who is the HIGHEST paid C programmer?
- Who is the HIGHEST paid female COBOL programmer?
- Display the names of the HIGHEST paid programmer for EACH language (prof1).
- Who is the LEAST experienced programmer?
- Who is the MOST experienced male programmer knowing PASCAL?
- Which language is known by ONLY ONE programmer?
- Who is that above programmer?
- Who is the YOUNGEST programmer-knowing DBASE?
- Which female programmer earning MORE than 3000/- DOESN’T know C, C++, Oracle or DBASE?
- Which institute has the MOST NUMBER of students?
- Which course has been done by the MOST of the students?
- Display the name of the institute and course, WHICH has below AVERAGE course fee.
- Which us the COSTLIEST course?
- Which institute conducts the COSTLIEST course?
- Which course has below AVERAGE number of students?
- Which institute conducts the above course?
- Display the names of the course WHOSE fees are within 1000/- (+ or -) of the AVERAGE fee.
- Which package has the HIGHEST development cost?
- Which package has LOWEST selling cost?
- Which language was used to develop the package, which has the HIGHEST sales amount?
- Which language was used to develop the package, which has the HIGHEST sales amount?
- How many copies of the package that has the LEAST DIFFERENCE between development and selling cost, were sold?
- Which is the costliest package developed in PASCAL?
- Which language was used to develop the MOST NUMBER of packages?
- Which programmer has developed the HIGHEST number of packages?
- Who is the author of the COSTLIEST package?
- Display the names of the package, WHICH have sold LESS THAN the AVERAGE number of copies.
- Who are the authors of the package, WHICH have recovered MORE THAN double the development cost?
- Display the programmer names and the CHEAPEST package developed by them in EACH language.
- Display the language used by EACH programmer to develop the HIGHEST selling and the LOWEST selling package.
- Who is the YOUNGEST male programmer born in 1965?
- Who is the OLDEST female programmer WHO joined in 1992?
- In WHICH year were the MOST NUMBER of programmers born.
- In WHICH month did MOST NUMBER of the programmers join.
- In WHICH language are MOST of the programmers proficient.
- Who are the male programmers earning BELOW the AVERAGE salary of female programmers?
- 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
- Which language has been started as prof1 by the MOST of the programmers.
- Display the details of the software development by the male programmer earning MORE than 3000.
- Display the details of the package developed in PASCAK by female programmers.
- Display the details of those programmers WHO joined BEFORE 1990.
- Display the details of the software developed in DBASE by female programmers of SAKTHIDBTECH.
- Display the NUMBER of packages, NUMBER of copies sold and sales value of EACH programme, institute-wise.
- Display the details of the software developed in DBASE by male programmers WHO belong to the institute in WHICH MOST NUMBER of programmer studied.
- Display the details of the software developed by the male programmers born BEFORE 1995 and female programmers AFTER 1975.
- Display the details of the software that was developed in the language that is NOT the programmer’s first proficiency.
- Display the details of the software that was developed in the language WHICH is NEITHER the first NOT the second proficiency of the programmer.
- Display the details of the software developed by the students of SDBT.
- Display the names of the programmers WHO HAVE NOT developed any package.
- What is the total cost of the software developed by the programmers by APPLE?
- Who are the programmers WHO JOINED on the same day?
- Who are the programmers WHO HAVE the same prof2?]
- Display the total sales value of software, institute wise.
- In which institute did the person WHO developed the COSTILIEST package study.
- Which language lasted in prof1 and prof2 HAS NOT BEEN used to develop any package?
- How many does the person WHO developed the HIGHEST selling package earn and WHAT course did he/she undergo?
- How many months will take for each programmer to recover the cost of the course underwent.
- Which is the COSTILIEST package developed by a person with under 3 years experience?
- What is the AVERAGE salary for those WHOSE software’s sales value is more than 50,000?
- How many packages were developed by students WHO studies in the institute that charge the LOWEST course fee,
- How many packages were developed by the person WHO developed the CHEAPEST package, WHERE did he/she study?
- How many packages were developed by female programmers earning MORE than the HIGHEST paid male programmer.
- How many packages were developed by MOST experienced programmer from MICROSOFT.
- List the programmers (from the software table) and the Institutes they studied, including those WHO DIDN’T develop any package.
- List each prof1 with the number of programmers having that prof1 and the number of packages developed in that prof1.
- 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.
- Find the sum of salary and comm (Salary+Comm)
- List the words that have 2 ‘A’s.
- List Name and 3 characters after 2rd ‘A’ of the words.(ex in SAI CHARAN, get ‘RAN ‘, in PADMANABHAN A, get ‘NAB’
- 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
- what is the key word used with Update?
- what is the key words to insert data in table?
- What is the query used to insert multiply column is single query?
- rollback can be used after truncate?
- Difference between delete and truncate?
- command used to save or undo all changes.
- How to fetch all tables?
- How to modify datatype query?
- what will happen after using delete?
- how to drop a column in a table.
- difference between truncate, delete and drop related questions
- How will you get the source code of a Table/INDEX/VIEW/SYNONYM .Hint(DBMS package)
- How will you check the size of a table / index?
- For which column you will create a index.
- What are the types of index?
- How to drop a record with child record exists
- Is it possible create two tables with same anem “sdbt” and “SDBT” (upper and Lower case)
- Is DDL Autocommit?
- What is the dml statement which is used for Insert as well as update.
- How will find your age using sysdate and your DOB using dual.
- How will drop a disable a primary key from a table.
- How will you truncate all tables from a given schema?
- How to check the status of a constraint?