21-30 câu SQL
10 Questions
8 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

  1. (0.150 Point) Which of the following SELECT statements lists the highest retail price of all books in the Family category?

  • A. SELECT MAX(retail) FROM books WHERE category - 'FAMILY': (correct)
  • B. SELECT MAX(retail) FROM books HAVING category = 'FAMILY';
  • C. SELECT retail FROM books WHERE category 'FAMILY' HAVING MAX(retail);
  • D. None of the above

    1. (0.150 Point) Consider the following schema: T_CUSTOMER(CUST_ID, CUST_NAME, CUST_ADDRESS) T_ORDER(ORDER_ID, ORDER DATE, CUST_ID) What are the names of all customers who have placed orders?

  • A. SELECT T_CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM T_CUSTOMER, T_ORDER
  • B. SELECT T_CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM T_CUSTOMER WHERE T_CUSTOMER.CUSTOMER_ID=T_ORDER.CUSTOMER_ID;
  • C. SELECT T_CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM T_CUSTOMER, T_ORDER WHERE T_CUSTOMER.CUSTOMER_ID=T_ORDER.CUSTOMER_ID; (correct)
  • D. None of the above

    1. (0.150 Point) Consider the following schema: T_CUSTOMER(CUST_ID, CUST_NAME, CUST_ADDRESS) T_ORDER(ORDER_ID, ORDER_DATE, CUST_ID) List customer name, identification number, and order number for all order listed in the ORDER table. Include order number even if there is no customer name and identification number available.

  • A. SELECT T_CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM T_CUSTOMER LEFT JOIN T_ORDER ON T_CUSTOMER.CUSTOMER_ID=T_ORDER.CUSTOMER_ID;
  • B. SELECT T_CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM T_CUSTOMER RIGHT JOIN T_ORDER ON T_CUSTOMER.CUSTOMER_ID=T_ORDER.CUSTOMER_ID; (correct)
  • C. SELECT T_CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER ID FROM T_CUSTOMER INNER JOINT ORDER ON T_CUSTOMER.CUSTOMER_ID=T_ORDER.CUSTOMER ID;
  • D. SELECT T_CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM T_CUSTOMER,T ORDER WHERE T_CUSTOMER.CUSTOMER_ID=T_ORDER.CUSTOMER_ID,

    1. (0.150 Point) Select DatePart(yy, '20/01/2021') As 'Năm'

    <p>B. 2021</p> Signup and view all the answers

    1. (0.150 Point) Table: Customer(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS) Give the following authorization command GRANT SELECT ON T_CUSTOMER(CUSTOMER_NAME, CUSTOMER_ADDRESS) TO USER The user that executes the SELECT * FROM T_CUSTOMER statement after being authorized is true, false or not possible

    <p>B. FALSE</p> Signup and view all the answers

    1. (0.150 Point) Which of the following SQL statements are used to establish a security scheme for a database?

    <p>C. Grant, Revoke</p> Signup and view all the answers

    1. (0.150 Point) A trigger fires in response to .....and ...statements

    <p>C. Insert, Delete, Update</p> Signup and view all the answers

    1. (0.150 Point) Consider the following steps:
    • Transaction A retrieves record R at time T
    • Transaction B retrieves the same record at time T + 1
    • Transaction A updates the record at time T+2 (base on value seen at time T)
    • Transaction B updates the same record at time T+3 (base on value seen at time T+1) What is the result of the above sequence of steps?

    <p>B. Transaction A's update would be overwritten by transaction B's update B1</p> Signup and view all the answers

    1. (0.150 Points) Which of the following statements contains an error?

    <p>D. SELECT empid WHERE empid = 56949 AND lastname = 'SMITH';</p> Signup and view all the answers

    1. (0.150 Points) What are the benefits of Triggers?

    <p>D. All of the above</p> Signup and view all the answers

    Study Notes

    SQL Query for Maximum Retail Price

    • The query should find the highest retail price for books in the "Family" category.

    Retrieving Customer Names

    • The query should retrieve customer names from the "T_CUSTOMER" table.
    • The query should include names of only those customers who have placed orders in the "T_ORDER" table.

    Combining Customer and Order Information

    • The query should retrieve information about customers and their orders.
    • The query should retrieve customer name, identification number, and order number.
    • Information should be retrieved from both the "T_CUSTOMER" and "T_ORDER" tables.
    • The query should include order numbers even if no corresponding customer information exists.

    Using DatePart Function

    • The query uses the DatePart function to extract the year component from the date '20/01/2021'.
    • The query then assigns the extracted year to a column named "Năm".

    SQL Authorization

    • The provided authorization command grants the user "SELECT" privileges.
    • The command allows the user to access specific columns (CUSTOMER_NAME and CUSTOMER_ADDRESS) in the T_CUSTOMER table.
    • After authorization, the user can execute the "SELECT * FROM T_CUSTOMER" statement, which retrieves all columns from the T_CUSTOMER table.
    • The statement will likely execute successfully since the user has been granted the necessary privileges.

    Defining a Security Scheme

    • SQL statements used to establish a security scheme for a database are responsible for managing access control and permissions.
    • Examples of such statements include GRANT, REVOKE, CREATE ROLE, and CREATE USER.

    Trigger Response

    • A trigger is activated in response to certain events.
    • Events that trigger a trigger include Data Modification Language (DML) operations.
    • DML operations include Insert, Update, and Delete statements.

    Transaction Isolation and Conflicts

    • The provided scenario demonstrates a potential conflict in a database system.
    • Both transactions A and B access the same record (R).
    • Transaction A updates the record based on its original value seen at time T.
    • Transaction B updates the record based on its value after being updated by A at time T+2.
    • This sequence of steps can lead to a scenario called a "lost update" or "dirty read".
    • Transaction B may read a value that was written by A but has not been committed, resulting in inconsistent data.

    Error in SQL Statements

    • The given context does not provide any specific SQL statements to analyze for errors.
    • Without specific statements, it is impossible to determine which one might contain an error.

    Benefits of Triggers

    • Triggers offer a way to enforce database rules and constraints automatically.
    • Triggers provide a mechanism to carry out an action in response to a specific event.
    • They can be used to maintain data integrity, track changes, or trigger other processes.
    • By encapsulating actions within triggers, developers can separate business logic from the main data manipulation code.

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Description

    This quiz focuses on understanding SQL SELECT statements specifically aimed at retrieving data about books. Test your knowledge on how to extract the highest retail price from books in the Family category. Enhance your SQL skills with this practical question.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser