SQL Fundamentals Quiz Quiz

1. Student Name Query

   Consider a STUDENTS table with columns like student_code, first_name, last_name, etc. Which SQL query would display all students where the second letter in their first name is 'i'?

   1. A - select first_name from students where first_name like '_i%';
   2. B - select first_name from students where first_name like '%i_';
   3. C - select first_name from students where first_name like '%i%';
   4. D - select first_name from students where first_name like '_i_';
   5. E - select firstname from student where firstname like '_i%';

   Explanation: The `_` character in `LIKE` matches exactly one character, and `%` matches zero or more characters.

2. SUBSTR Function

   What does the SQL function SUBSTR('TUTORIALS POINT', -1, 1) return?

   1. A - T
   2. B - NULL
   3. C - 0
   4. D - N
   5. E - TUTORIALS

   Explanation: SUBSTR with a negative starting position counts back from the end of the string.

3. SQL Join Truth

   Which of the following statements is true regarding SQL joins?

   1. A - The join condition is not separated from other search conditions in a query.
   2. B - The ON clause makes code difficult to understand.
   3. C - The join condition for natural join is basically an equijoin of all columns with the same name.
   4. D - None of the above.
   5. E - The join condition for natural join is basically an equijoin of all columns with different name.

   Explanation: Natural joins implicitly join tables based on columns sharing the same name and data type.

4. SQL Join Falsehood

   Which of the following statements is NOT true about SQL joins?

   1. A - An inner join is a join of two tables returning only matching rows.
   2. B - A left or right outer join returns the results of the inner join as well as the unmatched rows in the left or right table respectively.
   3. C - A full outer join returns results of an inner join as well as the results of a left and right join.
   4. D - None of the above.
   5. E - An inner join returns all rows.

   Explanation: All the statements about inner, left/right outer, and full outer joins are true.

5. COUNT Function

   Which of the following is NOT true about the COUNT function in SQL?

   1. A - COUNT(*) returns the number of rows in the table.
   2. B - COUNT(exp) returns the number of rows with non-null values for the exp.
   3. C - COUNT(DISTINCT exp) returns the number of unique, non-null values in the column.
   4. D - All are true.
   5. E - COUNT(NULL) returns the number of rows in the table.

   Explanation: All given statements are correct descriptions of the COUNT function's behavior.

6. Removing Rows From Table

   Which statement about removing rows from a table using SQL is true?

   1. A - You remove existing rows from a table using the DELETE statement.
   2. B - No rows are deleted if you omit the WHERE clause.
   3. C - You cannot delete rows based on values from another table.
   4. D - All of the above.
   5. E - You remove rows existing rows from a table using the UPDATE statement.

   Explanation: The `DELETE` statement is used to remove rows. While you can delete all rows if you omit the WHERE clause (effectively emptying the table), that is not the default/required behaviour. You *can* delete rows based on values from another table using subqueries.

7. CREATE TABLE

   Which of the following is true about the CREATE TABLE statement in SQL?

   1. A - This is a DML statement.
   2. B - This statement also record information in the data dictionary.
   3. C - You don't need any privilege to use this statement.
   4. D - All of the above.
   5. E - This is a DQL Statement

   Explanation: CREATE TABLE is a DDL (Data Definition Language) statement. It requires privileges and updates the data dictionary.

8. SQL Constraint Truth

   Which of the following is NOT true about constraints in SQL?

   1. A - A NOT NULL constraint specifies that the column cannot have a null value.
   2. B - A UNIQUE constraint specifies that a column or a combination of columns must have unique values for all rows.
   3. C - A PRIMARY KEY is same as UNIQUE.
   4. D - A FOREIGN KEY enforces a foreign key relationship between a column and a referenced table.
   5. E - A PRIMARY KEY is similar to a UNIQUE constraint, except it cannot contain NULL values.

   Explanation: A PRIMARY KEY is similar to a UNIQUE constraint, but it cannot contain NULL values and a table can only have one PRIMARY KEY.

9. Pseudocolumns Truth

   Which of the following is NOT true about Pseudocolumns that return the sequence values?

   1. A - NEXTVAL returns the next available sequence value.
   2. B - CURRVAL gets the current sequence value.
   3. C - PREVVAL gets the previous sequence value.
   4. D - None of the above.
   5. E - LASTVAL returns the last sequence value

   Explanation: There is no standard pseudocolumn called `PREVVAL`. `CURRVAL` retrieves the current value, and `NEXTVAL` generates the next value.

10. Granting Privileges

    Which code snippet correctly grants the privileges of creating tables and views to a role named 'student_admin'?

    1. A - grant create table, create view to student_admin;
    2. B - grant to student_admin create table, create view;
    3. C - grant role student_admin create table, create view;
    4. D - None of the above.
    5. E - grant create view to student_admin create table;

    Explanation: The `GRANT` statement syntax is `GRANT privilege1, privilege2 TO role_name;`

11. LIKE operator

    Which of the following SQL statements uses the LIKE operator to find names starting with 'A'?

    1. A - SELECT name FROM table WHERE name LIKE 'A%';
    2. B - SELECT name FROM table WHERE name LIKE '%A';
    3. C - SELECT name FROM table WHERE name = 'A';
    4. D - SELECT name FROM table WHERE name LIKE '_A';
    5. E - SELECT name FROM table WHERE name CONTANIS 'A';

    Explanation: The `LIKE` operator with 'A%' finds names starting with 'A'.

12. DISTINCT Keyword

    What is the purpose of the DISTINCT keyword in a SELECT statement?

    1. A - It sorts the result set.
    2. B - It filters rows based on a condition.
    3. C - It returns only unique values.
    4. D - It counts the number of rows.
    5. E - It combines multiple tables.

    Explanation: The `DISTINCT` keyword removes duplicate rows from the result set.

13. WHERE Clause

    Which part of a SQL query specifies the condition to filter rows?

    1. A - SELECT
    2. B - FROM
    3. C - WHERE
    4. D - GROUP BY
    5. E - ORDER BY

    Explanation: The `WHERE` clause is used to specify a condition for filtering rows.

14. ORDER BY Clause

    How to sort a table in descending order based on a column?

    1. A - ORDER BY column ASC
    2. B - SORT BY column DESC
    3. C - ORDER BY column DESC
    4. D - SORT BY column ASC
    5. E - GROUP BY column DESC

    Explanation: The `ORDER BY` with the `DESC` keyword sorts in descending order.

15. Joining Tables

    Which SQL keyword is used to combine rows from two or more tables based on a related column?

    1. A - UNION
    2. B - INTERSECT
    3. C - JOIN
    4. D - EXCEPT
    5. E - MERGE

    Explanation: The `JOIN` keyword is used to combine rows from two or more tables.