Untitled Quiz
33 Questions
0 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 are the four broad categories of SQL?

Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL)

Which of these is a function used to get the average of a column?

  • SUM()
  • MIN()
  • AVG() (correct)
  • MAX()

    • The SQL Server data type ______ is used to store whole numbers with no decimal places.

    INT

    VARCHAR and NVARCHAR data types are used to store variable-length text.

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

    What does the SQL Server data type DATETIME store?

    <p>Date and time values.</p> Signup and view all the answers

    What are the two main components that define a DECIMAL data type in SQL Server?

    <p>Precision and scale.</p> Signup and view all the answers

    The BIT data type in SQL Server is used to store Boolean values, which can be either 1 or 0.

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

    Which of these SQL commands is used to insert new rows into a table?

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

    What is the main purpose of the WHERE clause in an SQL statement?

    <p>To filter the data based on specific conditions.</p> Signup and view all the answers

    Explain the difference between the AND and OR operators in SQL.

    <p>The AND operator requires both conditions to be true for the statement to return true, while the OR operator returns true if at least one of the conditions is true.</p> Signup and view all the answers

    What is the purpose of the BETWEEN operator in SQL?

    <p>The BETWEEN operator enables you to filter data based on a range of values, indicating whether the value lies within the specified range.</p> Signup and view all the answers

    What does the LIKE operator achieve in SQL queries?

    <p>The LIKE operator allows you to filter data based on a pattern match, checking if a column value contains a specific pattern.</p> Signup and view all the answers

    Explain the purpose of the IN operator in SQL.

    <p>The IN operator compares a single column value with a list of values, checking if the value is present within that list.</p> Signup and view all the answers

    Match the following SQL operators with their appropriate description.

    <p>AND = Returns true only if both conditions are true OR = Returns true if at least one of the conditions is true NOT = Returns the opposite of the Boolean value it precedes BETWEEN = Filters values within a specified range, inclusive of the boundaries LIKE = Performs a pattern match, checking if a value contains a specific pattern IN = Compares a value with a set of values and returns true if the value exists within that set</p> Signup and view all the answers

    What is the difference between DELETE and TRUNCATE commands in SQL?

    <p>DELETE removes rows individually, while TRUNCATE removes all rows in a table at once. DELETE allows for conditional filtering, while TRUNCATE operates on the entire table.</p> Signup and view all the answers

    The SQL JOIN clause helps combine data from multiple tables based on common columns or a defined join condition.

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

    Which of these SQL join types returns all rows from the left table, along with matching rows from the right table, populating missing values on the right side with NULL?

    <p>LEFT JOIN</p> Signup and view all the answers

    The UNION operator combines result sets from multiple SELECT queries, removing duplicate rows.

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

    What is the difference between UNION and UNION ALL?

    <p>UNION removes duplicate rows, while UNION ALL keeps all rows, including duplicates.</p> Signup and view all the answers

    What is the purpose of the SQL INTERSECT operator?

    <p>The INTERSECT operator combines results from two queries, returning only the rows that are present in both result sets.</p> Signup and view all the answers

    How does the SQL CROSS JOIN differ from other join types?

    <p>The CROSS JOIN returns all possible combinations of rows from both tables, essentially creating the Cartesian product of the sets, regardless of any join conditions.</p> Signup and view all the answers

    A subquery can be used within a WHERE clause to filter data based on results from another query.

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

    What is the purpose of a correlated subquery in SQL?

    <p>A correlated subquery is executed once for each row in the outer query, referencing the outer query's values in the inner query, making it dependent on the outer query's rows.</p> Signup and view all the answers

    The SQL EXISTS operator can be used to check if a subquery returns any rows, impacting whether the main query executes or not.

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

    What is the primary function of the SQL GROUP BY clause?

    <p>To group rows in a result set based on common values in one or more columns, aggregating data into summary groups.</p> Signup and view all the answers

    The SQL HAVING clause can be used to filter the result set after grouping rows with GROUP BY, applying conditions to the aggregated data.

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

    What is the difference between using a WHERE clause before a GROUP BY and a HAVING clause after a GROUP BY?

    <p>The WHERE clause filters rows before grouping, while the HAVING clause filters groups after grouping, applying conditions to aggregate values.</p> Signup and view all the answers

    Aggregate functions in SQL can be used to summarize data within groups, such as finding minimum, maximum, average, sums, or the count of values.

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

    A subquery can only be used within a DELETE command, and it's not possible to use subqueries within other SQL commands like SELECT, UPDATE, or INSERT.

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

    SQL HAVING is typically used with SELECT and a GROUP BY clause, and it's less common to find it without a GROUP BY clause.

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

    What is a virtual table in the context of subqueries?

    <p>A virtual table created by a subquery that represents a set of rows, as if it were a separate table, allowing you to use the results of the subquery within the main query.</p> Signup and view all the answers

    SQL Subqueries are used when you want to compare a single value against a list of values, such as checking if a product has a specific code.

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

    Correlated subqueries execute once for each row in the outer query, making them less efficient than regular subqueries.

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

    Study Notes

    SQL (Structured Query Language)

    • SQL is a data manipulation language (DML)
    • Includes commands for inserting, updating, deleting, and retrieving data within database tables.
    • Also a data definition language (DDL) that creates database objects like tables, indexes, and views.
    • Commands define access rights to database objects

    SQL Data Types

    • Define characteristics of data stored in database columns.
    • Define possible values accepted.
    • INT (Integer): Accepts whole numbers only.
    • VARCHAR/NVARCHAR: Variable-length character strings.
    • DATETIME: Stores dates and times.
    • DECIMAL: Precise decimal values (numeric values with decimal point).
    • FLOAT: Long numeric values that are approximations.
    • BIT: Boolean values (Yes/No, True/False).

    Reasons to use SQL Server Data Types

    • Stores data consistently and in a known format.
    • Data type allows for specific calculations and formulations.
    • Data Types affect storage size (some values take up more space than others.)
    • Affects query performance.

    Commonly used SQL Server Data Types

    • INT: Stores whole numbers (-2,147,483,648 to 2,147,483,647)
    • VARCHAR/NVARCHAR: Stores variable length text (with maximum capacity of 8,000 characters for VARCHAR, 4,000 characters for NVARCHAR).
    • DATETIME: Stores dates and times from January 1, 1753 to December 31, 9999.
    • DECIMAL: Precise decimal values (precision and scale define number of total digits and digits after decimal point).
    • FLOAT: Stores approximate numerical values (huge range of numerical values.).
    • BIT: Stores boolean (True/False or 1/0) values.

    Data Manipulation Commands

    • INSERT: Used for inserting new rows into a table. Includes values for each column.
    • UPDATE: Modifies data in a table, specifying the column and new values. Uses the WHERE clause to target specific rows, optionally using AND or OR to filter based on multiple conditions, in case more rows need updating.
    • DELETE: Removes rows from a table. Uses the WHERE clause to target specific rows for deletion, optionally using AND or OR to filter based on multiple conditions.
    • SELECT: Retrieves data from one or more tables.
    • COMMIT: Permanently saves data changes.
    • ROLLBACK: Restores data to their original values before the last COMMIT.

    Special Operators

    • BETWEEN: Checks if a value falls within a specified range of values.
    • IS NULL: Checks if a value is null.
    • LIKE: Checks if a value matches a given pattern (using wildcards such as % for any sequence of characters, and _ for a single character).
    • IN: Checks if a value is present in a given list of values.
    • EXISTS: Checks if a subquery returns any rows.

    SQL Operators (AND, OR)

    • AND: Returns TRUE if both conditions are TRUE.
    • OR: Returns TRUE if either condition is TRUE (or both).

    Data Types (considerations)

    • CHAR, VARCHAR and VARBINARY data types that are padded with spaces in the SET ANSI_PADDING setting.
    • TRUNCATE TABLE deletes rows from a table.
    • DELETE statement deletes rows from a table based on a condition.
    • DELETE without a WHERE clause deletes all rows from the table.

    Additional Considerations

    • Data Type Conversion: Database errors occur if the values have the wrong data type.
    • Error Handling: TRY...CATCH statement can handle errors during INSERT, UPDATE, and DELETE operations.
    • UNIQUE: constraint ensures unique values in each row.
    • PRIMARY KEY: Marks a specific field.
    • DEFAULT values, CHECK constraint and NOT NULL constraint.
    • HAVING clause: Limits or sets conditions for the GROUP BY clause.

    Useful Functions

    • COUNT: Calculates the number of rows.
    • SUM: Calculates the sum of values within a column.
    • AVG: Calculates the average of values in a column.

    Studying That Suits You

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

    Quiz Team

    Related Documents

    IM101 - REVIEWER FINALS (1) - SQL Tutorial PDF

    More Like This

    Untitled Quiz
    6 questions

    Untitled Quiz

    AdoredHealing avatar
    AdoredHealing
    Untitled Quiz
    37 questions

    Untitled Quiz

    WellReceivedSquirrel7948 avatar
    WellReceivedSquirrel7948
    Untitled Quiz
    18 questions

    Untitled Quiz

    RighteousIguana avatar
    RighteousIguana
    Untitled Quiz
    48 questions

    Untitled Quiz

    StraightforwardStatueOfLiberty avatar
    StraightforwardStatueOfLiberty
    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser