Questions on SQL to test a data science professional

Q21) Which of the following statement(s) is/are true about "HAVING" and "WHERE" clause in SQL? WHERE" is always used before "GROUP BY" and HAVING after "GROUP BY" WHERE" is always used after "GROUP BY" and "HAVING" before "GROUP BY" "WHERE" is used to filter rows but "HAVING" is used to filter groups "WHERE" is used to filter groups but "HAVING" is used to filter rows Select the correct option: A. 1 and 3 B. 1 and 4 C. 2 and 3 D. 2 and 4

Solution: A HAVING is performed after GROUP BY. If you have to apply some conditions to get results. you need to use WHERE before group by.

28) Suppose you want to compare three keys ('Primary Key', 'Super Key' and 'Candidate Key') in a database. Which of the following option(s) is/are correct? Minimal super key is a candidate key Only one Candidate Key can be Primary Key All super keys can be a candidate key We cannot find "Primary Key" from "Candidate Key" Select the correct option: A. 1 and 2 B. 2 and 3 C. 1 and 3 D. 2 and 4 E. 1, 2 and 3

Solution: A Options are self-explanatory

Q25) What is the difference between TRUNCATE, DELETE and DROP? Which of the following statement(s) is/ are correct? DELETE operation can be rolled back but TRUNCATE and DROP operations cannot be rolled back. DELETE operation cannot be rolled back but TRUNCATE and DROP operations can be rolled back. DELETE is an example of DML (Data Manipulation Language) but remaining are the examples of DDL (Data Definition Language). All are an example of DDL Select the correct option: A. 1 and 3 B. 2 and 3 C. 1 and 4 D. 2 and 4 E. None of the above

Solution: A Options are self-explanatory.

Q13) Which of the following command(s) is / are related to transaction control in SQL? A. ROLLBACK B. COMMIT C. SAVEPOINT D. All of the above

Solution: D All are related to transaction control in SQL.

Q4) (See slide 4) Which of the following is the correct outcome of the SQL query below? Query: SELECT name, cid FROM student, enrolled WHERE student.sid = enrolled.sid AND enrolled.grade = 'C' A. Returns the name of all students and their corresponding course ids B. Returns the name of students and their corresponding course id where they have received grade C C. Error D. None of these

Solution: B The above query first joined the ENROLLED and STUDENT tables then it will evaluate the WHERE condition and then it will return the name of students and corresponding course id where they received the grade of C.

Q8) (See slide 4) Which of the following query(s) will result in a successful insertion of a record in the STUDENT table? Query1: INSERT INTO student (sid, name, login, age, gpa) VALUES (53888, 'Drake', 'drake@cs', 29, 3.5) Query2: INSERT INTO student VALUES (53888, 'Drake', 'drake@cs', 29, 3.5) A. Both queries will insert the record successfully B. Query 1 will insert the record successfully and Query 2 will not C. Query 2 will insert the record successfully and Query 1 will not D. Both queries will not be able to insert the record successfully

Solution: A Both queries will successfully insert a row in table student. The Query 1 is useful when you want to Provide target table, columns, and values for new tuples and Query 2 is a Short-hand version of insert command

Q18) Which of the following statement is correct about 'CREATE TABLE' command while creating a table? A. We need to assign a datatype to each column B. We have flexibility in SQL. We can assign a datatype to column even after creating a table C. It is mandatory to insert atleast a single row while creating a table D. None of these

Solution: A Each column must possess behavioral attributes like data types and precision in order to build the structure of the table.

Q10) (See slide 4) Consider the following queries: Query1: select name from enrolled LEFT OUTER JOIN student on student.sid = enrolled.sid; Query2: select name from student LEFT OUTER JOIN enrolled on student.sid = enrolled.sid; Which of the following option is correct? A. Queries 1 and 2 will give the same results B. Queries 1 and 2 will give different results C. Query 1 will produce an error and Query 2 will run successfully D. Query 2 will produce an error and Query 1 will run successfully

Solution: A In (LEFT, RIGHT or FULL) OUTER joins, order matters. But both query will give the same results because both are dependent on records present in table and which column in selected.

Q19) Which of the following are the synonyms for 'column' and 'row' of a table? Row = [Tuple, Record] Column = [Field, Attribute] Row = [Tuple, Attribute] Columns = [Field, Record] Select the correct option: A. 1 and 2 B. 3 and 4 C. Only 1 D. Only 2

Solution: A In DBMS records are also known as tuple and rows. And columns are known as attributes and fields.

Q16) Which of the following statement(s) is/are true for UPDATE in SQL? You can update only a single table using UPDATE command You can update multiple tables using UPDATE command In UPDATE command, you must list what columns to update with their new values (separated by commas). To update multiple targeted records, you need to specify UPDATE command using the WHERE clause. Select the correct option: A. 1, 3 and 4 B. 2, 3 and 4 C. 3 and 4 D. 1 only

Solution: A Options are self-explanatory.

Q11) (See slide 4) Which of the following statements will modify the data type of "Sid" column in ENROLLED table? Note: There is no foreign key relationship between tables STUDENT and ENROLLED. A. ALTER TABLE ENROLLED MODIFY (sid varchar(100)); B. ALTER TABLE ENROLLED MODIFY sid varchar(100); C. ALTER TABLE ENROLLED MODIFY column (sid varchar(100)); D. ALTER TABLE ENROLLED MODIFY attribute (sid varchar(100));

Solution: A The "ALTER TABLE MODIFY" is used to modify column definition in a table. So option A is correct.

Q5) (See slide 4) Which of the following is the correct outcome of the SQL query below? Query: SELECT student.name, enrolled.grade FROM student, enrolled WHERE student.sid = enrolled.sid AND enrolled.cid = '15-415' AND enrolled.grade IN ('A, 'B') A. Returns the name, grade of the students who took course '15-415′ and got a grade' A' or 'B' in that course B. Returns the name, grade of the students who took the course '15-415′ but didn't get grade 'A' or 'B' in that course C. Error D. None of these

Solution: A The above query first joined the ENROLLED and STUDENT tables then it will evaluate the where condition and then it will return the name, grade of the students, those took 15-415 and got a grade 'A' or 'B' in the course. But for the given two tables it will give zero records in output.

Q2) (See slide 4) Which of the following is the correct outcome of the SQL query below? Query: SELECT cid FROM ENROLLED WHERE grade = 'C' A. Extract the course ids(cid) where student receive the grade C in the course B. Extract the unique course ids(cid) where student receive the grade C in the course C. Error D. None of these

Solution: A The query will extract the course ids where student receive the grade "C" in the course.

1) Which of the following is the correct order of occurrence in a typical SQL statement? A. select, group by, where, having B. select, where, group by, having C. select, where, having, group byselect, having, where, group by D. select, having, where, group by

Solution: B "Where" always comes before "group by" and "having" always comes after "group by".

Q29) Consider a relation R with the schema R (A, B, C, D, E, F) with a set of functional dependencies F as follows: {AB->C, BC->AD, D->E, CF->B} Which of the following will be the output of DA+? Note: For any X, X+ is closure of X. A) DA B) DAE C) ABCD D) ABCDEF

Solution: B (DA)+ = DAE

Q7) (See slide 4) Which of the following statement will add a column 'F_name' to the STUDENT table? A. ALTER TABLE Student add column ( F_name varchar(20)); B. ALTER TABLE Student add F_name varchar(20); C. ALTER TABLE Student add (F_name varchar(20)); D. ALTER TABLE Student add column (F_name);

Solution: B ALTER TABLE command allows a user to add a new column to a table. Option B is correct syntax of ALTER to add a column in the table.

Q3) (See slide 4) Which of the following is the correct outcome of the SQL query below? Query: SELECT DISTINCT cid FROM ENROLLED WHERE grade = 'C' A. Extract the course ids where student receive the grade C in the course B. Extract the Distinct course ids where student receive the grade of C in the course C. Error D. None of these

Solution: B By using DISTINCT keyword you can extract the Distinct course ids where student receive the grade of C in the course.

Q22) (See slide 5) Identify, which of the following column "A" or "C" given in the below table is a "Primary Key" or "Foreign Key"? Note: We have defined 'Foreign Key' and 'Primary Key' in a single table A. Column 'A' is Foreign Key and Column 'C' is Primary Key' B. Column 'C' is Foreign Key and Column 'A' is 'Primary Key' C. Both can be 'Primary Key' D. Based on the above table, we cannot tell which column is 'Primary Key' and which is 'Foreign Key'

Solution: B Column A is taking unique values and column A doesn't have null values. So it can be considered as Primary key of this table. Whereas B is the example of foreign key because all values present in this column are already present in column A.

Q27) What is true about relation (table) in different normal forms (1NF, 2NF, 3NF)? If a relation satisfies the conditions of 1NF. It will automatically satisfy the conditions of 2NF If a relation satisfies the conditions of 2NF. It will automatically satisfy the conditions of 1NF If a relation satisfies the conditions of 3NF. It will automatically satisfy the conditions of 1NF If a relation satisfies the conditions of 2NF. It will automatically satisfy the conditions of 3NF Select the correct option: A. 1 and 2 B. 2 and 3 C. 1 and 3 D. 2 and 4

Solution: B If a relation is satisfying higher normal forms, it automatically satisfies lower normal forms also. For example, if a relation is satisfying kNF it will automatically satisfy gNF where g<=k.

Q20) Which of the following operator is used for comparing 'NULL' values in SQL? A. Equal B. IS C. IN D. None of Above

Solution: B In SQL if you want to compare a null value you need to use IS statement.

Q17) Which of the following is true for TRUNCATE in SQL? A. It is usually slower than DELETE command B. It is usually faster than DELETE command C. There is no comparison between DELETE & TRUNCATE D. Truncate command can be rolled back E. None of these

Solution: B TRUNCATE is faster than delete bcoz truncate is a ddl command so it does not produce any rollback information and the storage space is released while the delete command is a dml command and it produces rollback information too and space is not deallocated using delete command.

Q15) What is the difference between a primary key and a unique key? A. Primary key cannot be a date variable whereas unique key can be B. You can have only one primary key whereas you can have multiple unique keys C. Primary key can take null values but unique key cannot null values D. None of these

Solution: B You can create a date variable as a primary key in table. In relational schema, you can have only one primary key and there may be multiple unique key present in table. Unique key can take null values.

Q26) Tables A, B have three columns (namely: 'id', 'age', 'name') each. These tables have no 'null' values and there are 100 records in each of the table. Here are two queries based on these two tables 'A' and 'B': Query1: SELECT A.id FROM A WHERE A.age > ALL (SELECT B.age FROM B WHERE B.name = 'Ankit') Query2: SELECT A.id FROM A WHERE A.age > ANY (SELECT B.age FROM B WHERE B.name = 'Ankit') Now, which of the following statement is correct for the output of each query? A. The number of tuples in the output of Query 1 will be more than or equal to the output of Query 2 B. The number of tuples in the output of Query 1 will be equal to the output of Query 2 C. The number of tuples in the output Query 1 will be less than or equal to the output of Query 2 D. Can't say

Solution: C ANY and ALL operate on subqueries that return multiple values. ANY returns true if any of the subquery values meet the condition. But in case of ALL it will returns the records if all conditions are true. So options C is correct.

Q14) Which of the following is true for a primary key? A. It can take a value more than once B. It can take null values C. It can't take null values D. None of these

Solution: C In a relational schema, there exist only one primary key and it can't take null values. So option C is the correct answer.

Q9) (See slide 4) 'Sid' in "ENROLLED" table is 'Foreign Key' referenced by 'Sid' in "STUDENT" table. Now you want to insert a record into the ENROLLED table. Which of the following option(s) will insert a row in ENROLLED table successfully? INSERT INTO ENROLLED values(53667, '15-420', 'C'); INSERT INTO ENROLLED values(53666, '15-421', 'C'); INSERT INTO ENROLLED values(53667, '15-415', 'C'); INSERT INTO ENROLLED values(53666, '15-415', 'C'); A. 1 and 3 B. Only 3 C. 2 and 4 D. Only 4

Solution: C Option 2 and 4 will run successfully because in ENROLLED table's 'Sid' column you can insert those values which are present in STUDENT's table 'Sid' columns due to foreign key.

Q12) (See slide 4) Which of the following statement will remove the 'Sid' column from the ENROLLED table? Note: There is no foreign key relationship between tables STUDENT and ENROLLED. A. ALTER TABLE ENROLLED DROP (sid varchar(10) ); B. ALTER TABLE ENROLLED DROP COLUMN (sid varchar(10) ); C. ALTER TABLE ENROLLED DROP COLUMN Sid; D. ALTER TABLE ENROLLED MODIFY (sid);

Solution: C The "ALTER TABLE DROP COLUMN" can be used to drop a column from the table. So Option C is the right answer.

Q6) (See slide 4) Which of the following query will find all the unique students who have taken more than one course? A. SELECT DISTINCT e1.sid FROM enrolled AS e1, enrolled AS e2 WHERE e1.sid != e2.sid AND e1.cid != e2.cid B. SELECT DISTINCT e1.sid FROM enrolled AS e1, enrolled AS e2 WHERE e1.sid = e2.sid AND e1.cid = e2.cid C. SELECT DISTINCT e1.sid FROM enrolled AS e1, enrolled AS e2 WHERE e1.sid != e2.sid AND e1.cid != e2.cid D. SELECT DISTINCT e1.sid FROM enrolled AS e1, enrolled AS e2 WHERE e1.sid = e2.sid AND e1.cid != e2.cid

Solution: D Option D would be a right option. This query will first apply self join on enrolled table and then it evaluate the condition e1.sid = e2.sid AND e1.cid != e2.cid.