yZxVh7Q8

1SQL Interview Questions
2Advertisements
3Previous Page
4Next Page  
5
6Dear readers, these SQL Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of SQL. As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer:
7What is the difference between SQL and MySQL or SQL Server?
8
9SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.
10What is the difference between SQL and PL/SQL?
11
12PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.
13What are various DDL commands in SQL? Give brief description of their purposes.
14
15Following are various DDL or Data Definition Language commands in SQL −
16
17    CREATE − it creates a new table, a view of a table, or other object in database.
18
19    ALTER − it modifies an existing database object, such as a table.
20
21    DROP − it deletes an entire table, a view of a table or other object in the database.
22
23What are various DML commands in SQL? Give brief description of their purposes.
24
25Following are various DML or Data Manipulation Language commands in SQL −
26
27    SELECT − it retrieves certain records from one or more tables.
28
29    INSERT − it creates a record.
30
31    UPDATE − it modifies records.
32
33    DELETE − it deletes records.
34
35What are various DCL commands in SQL? Give brief description of their purposes.
36
37Following are various DCL or Data Control Language commands in SQL −
38
39    GRANT − it gives a privilege to user.
40
41    REVOKE − it takes back privileges granted from user.
42
43Can you sort a column using a column alias?
44
45Yes. A column alias could be used in the ORDER BY clause.
46Is a NULL value same as zero or a blank space? If not then what is the difference?
47
48A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.
49Say True or False. Give explanation if False.
50
51If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.
52
53True.
54If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?
55
56A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.
57What is the purpose of the condition operators BETWEEN and IN?
58
59The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.
60How do you search for a value in a database table when you don’t have the exact value to search for?
61
62In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called ‘wildcard’ search.
63What is the default ordering of data using the ORDER BY clause? How could it be changed?
64
65The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.
66What are the specific uses of SQL functions?
67
68SQL functions have the following uses −
69
70    Performing calculations on data
71
72    Modifying individual data items
73
74    Manipulating the output
75
76    Formatting dates and numbers
77
78    Converting data types
79
80What are the case manipulation functions of SQL?
81
82LOWER, UPPER, INITCAP
83Which function returns the remainder in a division operation?
84
85The MOD function returns the remainder in a division operation.
86What is the purpose of the NVL function?
87
88The NVL function converts a NULL value to an actual value.
89What is the difference between the NVL and the NVL2 functions?
90
91The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.
92
93The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.
94What is the use of the NULLIF function?
95
96The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.
97Discuss the syntax and use of the COALESCE function?
98
99The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)
100
101It returns the first non-null expression given in the parameter list.
102Which expressions or functions allow you to implement conditional processing in a SQL statement?
103
104There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement.
105
106    Using CASE expression
107
108    Using the DECODE function
109
110You want to display a result query from joining two tables with 20 and 10 rows respectively. Erroneously you forget to write the WHERE clause. What would be the result?
111
112The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.
113What is the difference between cross joins and natural joins?
114
115The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.
116What is the purpose of the group functions in SQL? Give some examples of group functions.
117
118Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
119Say True or False. Give explanation if False.
120
121By default the group functions consider only distinct values in the set.
122
123By default, group functions consider all values including the duplicate values.
124Say True or False. Give explanation if False.
125
126The DISTINCT keyword allows a function consider only non-duplicate values.
127
128True.
129Say True or False. Give explanation if False.
130
131All group functions ignore null values.
132
133True.
134Say True or False. Give explanation if False.
135
136COUNT(*) returns the number of columns in a table.
137
138False. COUNT(*) returns the number of rows in a table.
139What’s wrong in the following query?
140
141SELECT subject_code, count(name)  
142FROM students;
143
144It doesn’t have a GROUP BY clause. The subject_code should be in the GROUP BY clause.
145
146   SELECT subject_code, count(name)
147   FROM students
148   GROUP BY subject_code;
149
150What’s wrong in the following query?
151
152   SELECT subject_code, AVG (marks)
153   FROM students
154   WHERE AVG(marks) > 75
155   GROUP BY subject_code;
156
157The WHERE clause cannot be used to restrict groups. The HAVING clause should be used.
158
159   SELECT subject_code, AVG (marks)
160   FROM students
161   HAVING AVG(marks) > 75
162   GROUP BY subject_code;
163
164Say True or False. Give explanation if False.
165
166Group functions cannot be nested.
167
168False. Group functions can be nested to a depth of two.
169What do you understand by a subquery? When is it used?
170
171A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.
172Say True or False. Give explanation if False.
173
174A single row subquery returns only one row from the outer SELECT statement
175
176False. A single row subquery returns only one row from the inner SELECT statement.
177Say True or False. Give explanation if False.
178
179A multiple row subquery returns more than one row from the inner SELECT statement.
180
181True.
182Say True or False. Give explanation if False.
183
184Multiple column subqueries return more than one column from the inner SELECT statement.
185
186True.
187What’s wrong in the following query?
188
189   SELECT student_code, name
190   FROM students
191   WHERE marks = 
192               (SELECT MAX(marks)
193                  FROM students
194                  GROUP BY subject_code);
195
196Here a single row operator = is used with a multiple row subquery.
197What are the various multiple row comparison operators in SQL?
198
199IN, ANY, ALL.
200What is the pupose of DML statements in SQL?
201
202The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.
203Which statement is used to add a new row in a database table?
204
205The INSERT INTO statement.
206Say True or False. Give explanation if False.
207
208While inserting new rows in a table you must list values in the default order of the columns.
209
210True.
211How do you insert null values in a column while inserting data?
212
213Null values can be inserted into a table by one of the following ways −
214
215    Implicitly by omitting the column from the column list.
216    Explicitly by specifying the NULL keyword in the VALUES clause.
217
218Say True or False. Give explanation if False.
219
220INSERT statement does not allow copying rows from one table to another.
221
222False. INSERT statement allows to add rows to a table copying rows from an existing table.
223How do you copy rows from one table to another?
224
225The INSERT statement can be used to add rows to a table by copying from another table. In this case, a subquery is used in the place of the VALUES clause.
226What happens if you omit the WHERE clause in the UPDATE statement?
227
228All the rows in the table are modified.
229Can you modify the rows in a table based on values from another table? Explain.
230
231Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.
232Say True or False. Give explanation if False.
233
234The DELETE statement is used to delete a table from the database.
235
236False. The DELETE statement is used for removing existing rows from a table.
237What happens if you omit the WHERE clause in a delete statement?
238
239All the rows in the table are deleted.
240Can you remove rows from a table based on values from another table? Explain.
241
242Yes, subqueries can be used to remove rows from a table based on values from another table.
243Say True or False. Give explanation if False.
244
245Attempting to delete a record with a value attached to an integrity constraint, returns an error.
246
247True.
248Say True or False. Give explanation if False.
249
250You can use a subquery in an INSERT statement.
251
252True.
253What is the purpose of the MERGE statement in SQL?
254
255The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.
256Say True or False. Give explanation if False.
257
258A DDL statement or a DCL statement is automatically committed.
259
260True.
261What is the difference between VARCHAR2 AND CHAR datatypes?
262
263VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.
264Say True or False. Give explanation if False.
265
266A DROP TABLE statement can be rolled back.
267
268False. A DROP TABLE statement cannot be rolled back.
269Which SQL statement is used to add, modify or drop columns in a database table?
270
271The ALTER TABLE statement.
272What is a view? Why should you use a view?
273
274A view is a logical snapshot based on a table or another view. It is used for −
275
276    Restricting access to data;
277    Making complex queries simple;
278    Ensuring data independency;
279    Providing different views of same data.
280
281Say True or False. Give explanation if False.
282
283A view doesn’t have data of its own.
284
285True.
286What is Next ?
287
288Further you can go through your past assignments you have done with the subject and make sure you are able to speak confidently on them. If you are fresher then interviewer does not expect you will answer very complex questions, rather you have to make your basics concepts very strong.
289
290Second it really doesn't matter much if you could not answer few questions but it matters that whatever you answered, you must have answered with confidence. So just feel confident during your interview. We at tutorialspoint wish you best luck to have a good interviewer and all the very best for your future endeavor. Cheers :-)