The Ultimate SQL Syntax And Functionality Knowledge Test

40 Questions | Total Attempts: 35

SettingsSettingsSettings
Please wait...
The Ultimate SQL Syntax And Functionality Knowledge Test

Since its creation in 1974, SQL has become the most popular programming language for relational database management. With its fine set of functions and easy to learn syntax, it provides the ability to store, manipulate, and retrieve data in databases. Do you have good hands at SQL programming? Well, take this test and see for yourself.


Questions and Answers
  • 1. 
    Nomalization reduces data redundancy.
    • A. 

      True

    • B. 

      False

  • 2. 
    Nomalization increases the isk of loosing data integity
    • A. 

      True

    • B. 

      False

  • 3. 
    An entity type is 2NF when all of its non-key attibutes are fully dependent on its primary key.
    • A. 

      True

    • B. 

      False

  • 4. 
    About the 1NF (one or more can be selected):
    • A. 

      Each cell in the table can have only one value

    • B. 

      Each cell in the table can have only one value, except for numerical values

    • C. 

      Each cell in the table can have only one value, except for character values

    • D. 

      It is possible to have more than one value, as long as the values are separated by a semicolon

    • E. 

      None of the above

  • 5. 
    If an entity type is in 3NF, then... (one or more can be selected):
    • A. 

      All attributes are functionally dependent on the primary key

    • B. 

      All attributes are transitively dependent on the primay key

    • C. 

      All attibutes are not transitively dependent on the primary key

    • D. 

      Complies with the 1NF, but not necessarily with the 2NF

    • E. 

      None of the above

  • 6. 
    You are designing a database table to store student data.
    • A. 

      I prefer to store the student age (number)

    • B. 

      I prefer to store the student date of bith (date)

    • C. 

      I will store both

    • D. 

      None.

  • 7. 
    A many-to-many relationship can be resolved by using a linking table
    • A. 

      True

    • B. 

      False

  • 8. 
    Regarding the secuity manager
    • A. 

      Sets-up a database connection

    • B. 

      It veirfies the user granted pemissions to execute queries

    • C. 

      Manages the buffer memory to optimize queies

    • D. 

      None of the above

  • 9. 
    Between DDL and DML
    • A. 

      DML compiler helps to creates tables

    • B. 

      DDL compiler helps to inset data

    • C. 

      MySQL only manages DML, not DDL

    • D. 

      None of the above

  • 10. 
    In crow's foot notation:
    • A. 

      An entity type is represented by a rectangle

    • B. 

      The name of entity type should be singular

    • C. 

      The name of the entity type goes in the upper pat of the rectangle

    • D. 

      All of the above

  • 11. 
    About SQL
    • A. 

      It is an object oiented programming language

    • B. 

      It descibes the the set of data to be retieved/modified without specifying how to compute it

    • C. 

      Can be embedded in java code

    • D. 

      SQL means: Stuctured Quality Language

    • E. 

      All of the above

  • 12. 
    You want to list all the databases existing in the curent MySql sever, which command would you use?
    • A. 

      SELECT DATABASE NAMES FROM MYSQL

    • B. 

      CREATE DATABASE

    • C. 

      SHOW DATABASES

    • D. 

      USE DATABASE NAMES

  • 13. 
    About primary key:
    • A. 

      Must be composed by one column only

    • B. 

      Can be composed by multiple columns

    • C. 

      Suppots not unique values if defined as varchar type

    • D. 

      It is a bad practice to leave tables without a primary key

  • 14. 
    What is true about the SQL dialect
    • A. 

      Refers to the oiginal SQL86 version

    • B. 

      The way SQL coders communicate to each other

    • C. 

      The SQL implementation provided by each vendor

    • D. 

      The SQL propety of being embedded in JAVA applications

  • 15. 
    CRUD refers to
    • A. 

      Create, Read, Update, Drop

    • B. 

      Create, Read, Update, Delete

    • C. 

      Create, Retieve, Update, Drop

    • D. 

      Collect, Read, Update, Delete

    • E. 

      None of the above

  • 16. 
    You want to select the employees that do not belong to any department
    • A. 

      SELECT * FROM employee WHERE depatment_id is null;

    • B. 

      SELECT * FROM employee WHERE depatment_id is not null;

    • C. 

      SELECT * FROM employee WHERE depatment_id in (null);

    • D. 

      SELECT * FROM employee IF EXISTS depatment_id;

  • 17. 
    Assume table REGION has 3 rows with region_name= 'Ameicas', 2 rows with region_name = 'Europe', and 4 rows with other world regions. How many rows updates the following SQL code: UPDATE region SET egion_name = 'America' WHERE region_name = 'Americas' AND region_name = 'Europe';
    • A. 

      0

    • B. 

      5

    • C. 

      3

    • D. 

      2

    • E. 

      4

  • 18. 
    About the following SQL code: DELETE FROM region LIMIT 2 WHERE region_name = 'Europe';
    • A. 

      Deletes 2 rows coresponding to Europe region

    • B. 

      Completes execution but deletes zero regions

    • C. 

      Deletes the two first rows regardless of which region they refer to

    • D. 

      Raises an error

  • 19. 
    If the following reasons are tue, for which of them this SQL code raises an eror: INSERT INTO depatment (`DEPARTMENT_ID`,`DEPARTMENT_NAME`,`LOCATION_ID`) VALUES (4, 'MARKETING2', 100, 1000);
    • A. 

      You are inserting a DEPARTMENT_ID that already exists and DEPARTMENT_ID is defined as pimay key

    • B. 

      The number of columns is different from the number of values

    • C. 

      You are inseting a value for LOCATION_ID (foreign key) that does not exist in table LOCATION, where LOCATION_ID is defined as NOT NULL

    • D. 

      All of the previous reasons

  • 20. 
    You want to get the unique first names of the employees that are from Portugal
    • A. 

      SELECT UNIQUE first_name FROM employees WHERE county = 'Potugal'

    • B. 

      SELECT DISTINCT first_name FROM employees WHERE country = 'Portugal'

    • C. 

      SELECT first_name FROM employees WHERE county = 'Portugal'

    • D. 

      None of the above

  • 21. 
    You want to find the products whose pices are between 200 and 400
    • A. 

      SELECT productName FROM products WHERE Pice > 200 AND < 400;

    • B. 

      SELECT productName FROM products WHERE Pice BIGGER 200 AND SMALLER 400;

    • C. 

      SELECT productName FROM products WHERE Pice BETWEEN 200 AND 400;

    • D. 

      SELECT productName FROM products WHERE Pice IN 200 AND 400;

  • 22. 
    About the left join, being table A the left table and B the ight table:
    • A. 

      Return all of the records in table A that do not match any records in the table table B

    • B. 

      Returns only the records in table A that match the records in table B.

    • C. 

      Returns all of the records in table A regardless if the records have a match in table B.

    • D. 

      Returns all of the records in table B regardless if the records have a match in table A

    • E. 

      None of the above

  • 23. 
    Assuming that emp_no is the pimay key, and that the job_title is unique. What the following query does? SELECT e.first_name, t.title, e.emp_no FROM employees AS e, titles AS t WHERE e.emp_no = t.emp_no AND t.title = 'Senior Engineer
    • A. 

      Retrieve the first name, title, and employee identifier of the senior engineers

    • B. 

      Retrieve the first name, title, and employee identifier of the employees that are not senior engineers

    • C. 

      Retrieve the first name, title, and employee identifier of employees where the employee identifies is equal to the title identifier

    • D. 

      None of the above

  • 24. 
    What retieves the following query? SELECT T1.orderNumber, T1.status, SUMT2.quantityOrdered * T2.piceEach) total FROM orders AS T1 INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber GROUP BY T1.orderNumber;
    • A. 

      OrderNumber, status, quantityOrdered, and piceEach

    • B. 

      OrderNumber, status, and the order average

    • C. 

      OrderNumber, status, and the total of the order

    • D. 

      OrderNumber, status, the total of each order detail

  • 25. 
    What is tue about the following query: SELECT o.orderNumber, p.productName, p.msrp, o.piceEach FROM products p JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.piceEach WHERE p.productcode = 'S10_1678';
    • A. 

      The query only retieves data from table products

    • B. 

      The query uses an inner join

    • C. 

      The query uses a ight join

    • D. 

      The query joins tables based on the msrp as key column

Related Topics
Back to Top Back to top