Important SQL questions and answers with practice questions

Sep 10 • Notes • 45636 Views • 10 Comments on Important SQL questions and answers with practice questions

 SQL stands for Structured Query Language. It’s a fourth generation language, used to store any information such as organisational details, bank details, production information, patient status in hospital, etc. in to the database. Database is a collection of interrelated data which are to be stored in a sequential and conceptual manner. Which helps a user to retrieve the required data or any information from the database in a small instance of time. Structure Query language is used for inserting, updating and modifying any data present in the database. Structure Query Language is very easy and are like English statement. We have discussed all the important sql questions taking care of all the basic concepts. Still if it contains any error please do let us know through the comments. Any type of suggestions will be highly appreciated.

SOLVED PAPER ON INTRODUCTION TO SQL

1.     What can SQL do?

sql

Key to Database !!

Normally SQL is used to store, update and retrieve the information from the database. After having some certain data, it is stored in the database through certain SQL commands. The data can be updated, inserted, deleted from the database through SQL. At the end, we can retrieve the required information from the database through SQL.

2.     Define the categories of Data Manipulation Language.

Data manipulation language (DML) is divided into three categories.

  • (a) Retrieve the data : Retrieving the required data from the database. The command for retrieving data is SELECT. 
  • (b) Manipulate the data :  This feature allows us to perform some arithmetic functions like summing, averaging, subtracting, dividing the data between two or more columns.
  • (c)  Update the Data: It means we can update the data like inserting data into table, deleting data from table, change the values in tables etc.

COMMANDS

  • SELECT – Retrieve data from the a database
  • INSERT – Insert data into a table or Database
  • UPDATE – Updates or Modifying existing data within a table or database
  • DELETE – Deletes all records from a table, the space for the records remain
  • MERGE – UPSERT operation (insert or update)
  • CALL – Call a PL/SQL or Java subprogram
  • EXPLAIN PLAN – explain access path to data
  • LOCK TABLE – Control concurrency to avoid Data leaking

3.     What are the data types in SQL?

The data types in SQL are listed below.

(a)  CHAR (size): A column is defined with a CHAR data type is allowed to store all types of characters which include letters both uppercase and lowercase, special characters like @,#,$,&,etc. and numerical values.
(b) VARCHAR2 (size): This data type is used to store string type variables where the size can have maximum 2000 characters.
(c)  NUMBER (p,s): This data type is used to store numerical data into the database. The value of p determines the total number of digits possible to the left of decimal point whereas the value of s determines the total number of digits possible to the right of decimal point.
(d) DATE :  This type of data type is used to store the date and time information in database. Default format is DD-MM-YY.
(e)  LONG:  This data type is used to store variable length character strings up to 2GB size.

4.     What are the limitations of LONG data type in SQL ?

There are certain limitations for use of  LONG data type in SQL. These are as follows:

(a)  A table cannot have more than one LONG data type.
(b) It cannot be indexed.
(c)  It cannot be used in SQL functions
(d) It cannot appear in WHERE, GROUP BY, ORDER BY clauses.

5.     Mention some important SQL commands.

Some of the important SQL commands are listed below.

  • SELECT                      – This command is used to extracts data from a database.
  • UPDATE                    – This command is used to update the data in a database.
  • DELETE                     This command is used to delete any data from database.
  • INSERT INTO           – This command is used to insert a new data into  the database.
  • CREATE DATABASE This command is used to create a new database.
  • ALTER DATABASE   – This command is used for any type of modification in a database.
  • CREATE TABLE        This command is used for creating a new table.
  • ALTER TABLE          This command is used for modification in the table.
  • DROP TABLE           This command is used to delete a table.
  • CREATE INDEX        This command is used to create an index.
  • DROP INDEX           – This command is used to delete an index.

6.     Consider the following table STUDENT.

REGD.NONAMEBRANCH
0001RamCSE
0002HariMECH
0003PradeepEEE
0004DeepakETC

 

Write a SQL command which will show the entire STUDENT table.

Ans:- The SQL command which will show all the information in STUDENT table is

      SELET * from STUDENT;

7.     Consider the following table STUDENT.
 

REGD.NONAMEBRANCH
0001RamCSE
0002HariMECH
0003PradeepEEE
0004DeepakETC

 Write down the SQL command which will show the Regd. No of Pradeep.

The SQL command which will show the Regd.No of Pradeep is

SELECT regd.no from STUDENT WHERE name=Pradeep;

8.     Consider the following table STUDENT.

 

REGD.NONAMEBRANCH
0001RamCSE
0002HariMECH
0003PradeepEEE
0004DeepakETC

Write down the SQL command which will show the Name and Branch column.

The SQL command which will show the NAME and BRANCH column is

        SELECT Name, Branch from STUDENT;

9.     Consider the following table STUDENT.

 

REGD.NONAMEBRANCH
0001RamCSE
0002HariMECH
0003PradeepEEE
0004DeepakETC

Write a SQL command which will count the number of rows existing in STUDENT table.

The SQL command which will count the number of rows present in STUDENT table is

        SELECT COUNT(*) FROM STUDENT;

10.     Consider the following table STUDENT.

 

REGD.NONAMEBRANCH
0001RamCSE
0002HariMECH
0003PradeepEEE
0004DeepakETC

I want to add another column in the STUDENT table as”address”. How can I do that?

To add another column in STUDENT table we can use ALTER command as

            Alter table student add address varchar2(30);

Important Questions on SQL : 

1. Query Emp table.
2. Select the employees in department 30.
3. List the names, numbers and departments of all clerks.
4. Find the department numbers and names of employees of all departments with deptno greater than 20.
5. Find employees whose commission is greater than their salaries.
6. Find employees whose commission is greater than 60 % of their salaries.
7. List name, job and salary of all employees in department 20 who earn more than 2000/-.
8. Find all salesmen in department 30 whose salary is greater than 1500/-.
9. Find all employees whose designation is either manager or president.
10. Find all managers who are not in department 30.
11. Find all the details of managers and clerks in dept 10.
12. Find the details of all the managers (in any dept) and clerks in dept 20.
13. Find the details of all the managers in dept. 10 and all clerks in dept 20 and all employees who are neither managers nor clerks but whose salary is more than or equal to 2000/-.
14. Find the names of anyone in dept. 20 who is neither manager nor clerk.
15. Find the names of employees who earn between 1200/- and 1400/-.
16. Find the employees who are clerks, analysts or salesmen.
17. Find the employees who are not clerks, analysts or salesmen.
18. Find the employees who do not receive commission.
19. Find the different jobs of employees receiving commission.
20. Find the employees who do not receive commission or whose commission is less than 100/-.
21. If all the employees not receiving commission is entitles to a bonus of Rs. 250/- show the net earnings of all the employees.
22. Find all the employees whose total earning is greater than 2000/- .
23. Find all the employees whose name begins or ends with ‘M’
24. Find all the employees whose names contain the letter ‘M’ in any case.
25. Find all the employees whose names are upto 15 character long and have letter ‘R’ as 3rd character of their names.
26. Find all the employees who were hired in the month of February (of any year).
27. Find all the employees who were hired on last day of the month.
28. Find all the employees who were hired more than 2 years ago.
29. Find the managers hired in the year 2003.
30. Display the names and jobs of all the employees separated by a space.
31. Display the names of all the employees right aligning them to 15 characters.
32. Display the names of all the employees padding them to the right up to 15 characters with ‘*’.
33. Display the names of all the employees without any leading ‘A’.
34. Display the names of all the employees without any trailing ‘R’.
35. Show the first 3 and last 3 characters of the names of all the employees.
36. Display the names of all the employees replacing ‘A’ with ‘a’.
37. Display the names of all the employees and position where the string ‘AR’ occurs in the name.
38. Show the salary of all the employees , rounding it to the nearest Rs. 1000/-.
39. Show the salary of all the employees , ignoring the fraction less than Rs. 1000/-.
40. Show the names of all the employees and date on which they completed 3 years of service.
41. For each employee, display the no. of days passed since the employee joined the company.
42. For each employee, display the no. of months passed since the employee joined the company.
43. Display the details of all the employees sorted on the names.
44. Display the names of the employees, based on the tenure with the oldest employee coming first.
45. Display the names, jobs and salaries of employees, sorting on job and salary.
46. Display the names, jobs and salaries of employees, sorting on descending order of job and within job sorted on salary.
47. List the employee names, department names and salary for those employees who have completed 1 year of service.
48. List the employee names, department names and salary for those employees who are earning 0 commission or commission is null. Sort your output in the order of department name.
49. List the employee names, department names and hiredate for those employees who have joined in 2003 . Sort your output in the order of joining date.
50. List all the department names along with the names of employees in them , irrespective of the fact whether any employee is there or not.

 Related Links
1. Questions on SQL
2. Interview Questions on SQL/ PL SQL
3. Connectivity on Mysql with the database
4. Database Management System

Tell us Your Queries, Suggestions and Feedback

Your email address will not be published.

10 Responses to Important SQL questions and answers with practice questions

  1. Anonymous says:
  2. Daniel says:

    I work in the healthcare financial world of billing, claims, and clinical data. The use of SQL is very prevalent and intense..I want to become an expert..I have beginners knowledge but I need expert knowledge.

  3. Daniel says:

    I work in the healthcare financial world of billing, claims, and clinical data. The use of SQL is very prevalent and intense..I want to become an expert..I have beginners knowledge but I need expert knowledge.

  4. rambandhu sharma says:

    13. Display the description of the Student table.
    14. Display the details of all students.
    15. Display unique majors.
    Apply
    sID
    cName
    major
    decision
    123
    Stanford
    CS
    Y
    123
    Stanford
    EE
    N
    123
    Berkeley
    CS
    Y
    123
    Cornell
    EE
    Y
    234
    Berkeley
    biology
    N
    345
    MIT
    bioengineering
    Y
    345
    Cornell
    bioengineering
    N
    345
    Cornell
    CS
    Y
    345
    Cornell
    EE
    N
    678
    Stanford
    history
    Y
    987
    Stanford
    CS
    Y
    987
    Berkeley
    CS
    Y
    876
    Stanford
    CS
    N
    876
    MIT
    biology
    Y
    876
    MIT
    marine biology
    N
    765
    Stanford
    history
    Y
    765
    Cornell
    history
    N
    765
    Cornell
    psychology
    Y
    543
    MIT
    CS
    N
    Student
    sID
    sName
    GPA
    sizeHS
    DoB
    123
    Amy
    3.9
    1000
    26-JUN-96
    234
    Bob
    3.6
    1500
    7-Apr-95
    345
    Craig
    3.5
    500
    4-Feb-95
    456
    Doris
    3.9
    1000
    24-Jul-97
    567
    Edward
    2.9
    2000
    21-Dec-96
    678
    Fay
    3.8
    200
    27-Aug-96
    789
    Gary
    3.4
    800
    8-Oct-96
    987
    Helen
    3.7
    800
    27-Mar-97
    876
    Irene
    3.9
    400
    7-Mar-96
    765
    Jay
    2.9
    1500
    8-Aug-98
    654
    Amy
    3.9
    1000
    26-May-96
    543
    Craig
    3.4
    2000
    27-Aug-98
    College
    cName
    state
    enrollment
    Stanford
    CA
    15000
    Berkeley
    CA
    36000
    MIT
    MA
    10000
    Cornell
    NY
    21000
    Harvard
    MA
    50040
    16. List the student names those are having three characters in their Names.
    17. List the student names those are starting with ‘H’ and with five characters.
    18. List the student names those are having third character and fifth char. must be ‘e’.
    19. List the student names ending with ‘y’.
    20. List the Students in the order of their GPA.
    21. List the details of the students in order of the ascending of GPA and descending of DoB.
    22. List the sIDs of student who apply in either ‘Stanford’, ‘Cornell’ or ‘MIT’ college.
    23. Delete all applications filled at Stanford (Choose table wisely)
    24. Delete the college Stanford from college table.
    25. Modify the GPA of all students by giving 10% raise in their GPA.
    26. Increment the GPA of the students by 1.5 whose GPA is less than 3.5 and belongs to High School having size greater than 1500.
    27. Delete the students who have scored less than 3.2 GPA.

  5. Vishal Aggarwal says:

    I WANT SOME PRACTICE QUESTIONS ON SQL SO THAT I HAVE SOME HANDS ON EXPERIENCE.PLEASE UPDATE ME AS SOON AS POSSIBLE.

  6. HFT says:

    As faг as eateries go, there is none ass funky
    or idiosyncratic as thᥱ Galata House, housed insidе tthe erstwhile British Jail.

    Ƭhе space іs filled with hіѕ whimsical art, funky ɑnd fabulous jjewelry and
    sumptuous handbags ɑnd textiles. Ι am not advocatong that we shoulԁn’tmake any attempt tο dress nicely oг drive а reasonhable
    ϲar ѡhich іs safe, oг live іn a nice neighbourhood.

  7. sana warriach says:

    the names of 8 males students of G.C.U .and gain 12 female students of l.C.U. arewritten on cards and placed in a box.eight names aur drawn without replacement which is the probability distributionof number of male students .compute the mean ,variance and C.V .of the probability distribution and verify the result

  8. geetha says:

    Display the names of all the employees without any leading A .

  9. g santhosh says:

    i am create 2tables with name person and department in person table pid is pk-key and did is fk-key and in deaprtmnt table did is pk-key and pid is fk-key .when i am aplyng i am not apply pk and fk keys and alter tables and apply the pk and fk keys with person but it was not posble when aplyng for departmnt table sir can u calfry my dout pls

  10. Anoj says:

    How to find matching coulumn from two tables without using where clause

« »