SQL Data Manipulation Language Quiz
13 Questions
2 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

What symbol is used in SQL to select all fields from a table?

  • #
  • .*
  • * (correct)
  • %

    • In SQL, which part of a query specifies the condition that must be met?

  • ORDER BY
  • FROM
  • SELECT
  • WHERE (correct)

    • What is the expected outcome of executing a query with 'WHERE Amount < 50' on a table?

  • All rows are returned regardless of the Amount.
  • Only the rows with an Amount less than 50 are returned. (correct)
  • All rows with an Amount greater than 50 are returned.
  • Only the rows with an Amount of 50 are returned.

    • Which of the following statements about SQL is true?

    <p>SQL allows duplication in relation and query results.</p> Signup and view all the answers

    When executing an SQL query, how does the system handle rows that do not meet the WHERE condition?

    <p>Such rows are ignored and not included in the result.</p> Signup and view all the answers

    Which SQL statement will eliminate duplicate records when retrieving Account and Amount from a table?

    <p>SELECT DISTINCT AcctNo, Amount FROM Deposit</p> Signup and view all the answers

    What is the result of the SQL statement SELECT AcctNo AS Number, Amount * 10 AS Amt FROM ATMWithdrawal WHERE Amount < 50?

    <p>Retrieves AcctNo and multiplies the Amount by 10, assigning new names.</p> Signup and view all the answers

    Which aggregate function would you use to find the highest balance in the Account table?

    <p>SELECT MAX(Balance) FROM Account</p> Signup and view all the answers

    In the context of SQL, what does the WHERE clause do?

    <p>It specifies the conditions to filter records returned from the query.</p> Signup and view all the answers

    To retrieve all ATM withdrawal records with an amount less than 50 where the transaction was not done by CustID 1, which statement is correct?

    <p>SELECT * FROM ATMWithdrawal WHERE Amount &lt; 50 AND CustID != 1;</p> Signup and view all the answers

    What does the keyword 'avg' do in an SQL statement?

    <p>It retrieves the average value of the specified numeric field.</p> Signup and view all the answers

    Which SQL statement aggregates the total number of customers in the Customer table?

    <p>SELECT COUNT(*) FROM Customer;</p> Signup and view all the answers

    Which logical operator can be used in a WHERE clause to combine multiple conditions?

    <p>AND</p> Signup and view all the answers

    Study Notes

    SQL: Data Manipulation Language (DML)

    • SQL (Structured Query Language) is a language used to manage and manipulate data in relational databases.
    • DML (Data Manipulation Language) is a subset of SQL used for querying and modifying data.

    Query Basic Structure

    • A basic SQL query structure involves selecting fields, specifying tables, and applying conditions.
    • The general format is: SELECT A1, A2, ..., An FROM R1, R2, ..., Rn WHERE P
    • A1, A2, ..., An: Selected fields. Using * selects all fields.
    • R1, R2, ..., Rn: Table names from which to retrieve data.
    • P: Requirement condition to filter results.

    Sample Data Tables

    • Account table: Stores account details (AcctNo, CustID, Balance, Type).
    • Deposit table: Tracks deposit transactions (TransactionID, AcctNo, Date, Amount).
    • Check table: Registers check transactions (CheckNumber, AcctNo, Date, Amount).
    • ATMWithdrawal table: Records ATM withdrawal transactions (TransactionID, CustID, AcctNo, Amount, WithdrawDate).
    • Customer table: Maintains customer information (CustID, Name, Phone, Address).

    Sample Data (ATMWithdrawal table)

    • TransactionID, CustID, AcctNo, Amount, WithdrawDate are example columns in ATMWithdrawal table.

    Displaying AcctNo and Amount

    • A query to display AcctNo and Amount from the ATMWithdrawal table is provided, showing different amounts and their corresponding account numbers.

    Query Answer Table

    • Shows the result of the SQL query, containing selected columns from a specific table (ATMWithdrawal) based upon the query.
    • The result is formatted in a table with designated columns and corresponding data.

    Sample Queries (with WHERE)

    • Retrieve data from ATMWithdrawal that meets a certain condition (e.g., Amount < 50).
    • The results of queries are shown with their respective column values matching the condition in the sample data from the ATMWithdrawal table.

    SQL Extensions

    • Extensions for the SELECT, FROM, and WHERE clauses modify the basic query structure.

    DISTINCT Keyword

    • Use DISTINCT after SELECT to eliminate duplicate rows in the results of a query.

    Arithmetic Expressions

    • SQL allows SELECT statements to include arithmetic calculations within the query.

    Using AS for New Attributes

    • New attributes can be generated, with a SELECT clause introducing AS modifier for renaming columns.

    Aggregate Functions

    • SELECT statements can use aggregate functions (AVG, MIN, MAX, SUM, COUNT). These generate results like the average, minimum value, maximum value, sum, and the number of values (or rows) from a table.
    • These functions are applied to a numeric column value within the table in question.

    WHERE Clause Extensions

    • Logical comparisons (AND, OR, NOT) are supported in WHERE clauses.
    • Comparison operators (=, >, >=, <, <=, <>) are allowed for complex filtering conditions.
    • Empty and non-empty checks (IS NULL, IS NOT NULL) can be used to filter rows based on whether a column contains a null value.
    • String operations (e.g., LIKE, wildcards %, _) filter rows matching specific string patterns.
    • Other operations like IN and BETWEEN allow for more complex filtering criteria such as checking if a value is in a list or lies between a range.

    Queries on Two Tables

    • Queries can involve multiple tables and use conditions to link (e.g. JOIN) results from different data tables.
    • Show examples of SQL queries on two different tables, Account and Deposit, that illustrate joining tables with common attributes.

    Studying That Suits You

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

    Quiz Team

    Related Documents

    SQL Data Manipulation Language (DML) PDF

    Description

    Test your knowledge of SQL and Data Manipulation Language (DML) with this quiz. Learn about the basic structure of SQL queries and how to manipulate data in relational databases. Challenge yourself with questions on SQL commands and sample data tables.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser