Questions and Answers
What does DML stand for?
Data Manipulation Language
In SQL, the GROUP BY clause can only produce summary values for selected columns.
True
The ORDER BY clause is used to ______ records in the resulting list.
sort
Which of the following are part of SQL DML? (Select all that apply)
Signup and view all the answers
What happens if you omit a WHERE clause in a DELETE statement?
Signup and view all the answers
What are the rules for inserting an empty string into a varchar or text column? (Select all that apply)
Signup and view all the answers
Which operation in SQL is used to update data in a table?
Signup and view all the answers
What does the ORDER BY clause do in SQL?
Signup and view all the answers
All char columns in SQL are left-padded to the defined length.
Signup and view all the answers
The ____ statement is used to remove rows from a record set in SQL.
Signup and view all the answers
Study Notes
Data Manipulation Language (DML) Overview
- SQL DML is used to query and modify database data.
- It consists of four main commands: SELECT, INSERT, UPDATE, and DELETE.
SELECT Statement
- Used to extract data from tables based on specific criteria.
- Processed in the following sequence: SELECT, FROM, WHERE, GROUP BY, and ORDER BY.
- Can be used with the DISTINCT keyword to remove duplicates.
SELECT Statement with WHERE Criteria
- Used to filter data based on conditions specified in the WHERE clause.
SELECT Statement with ORDER BY Clause
- Used to sort the results in ascending or descending order.
- ASC is used for ascending order and DESC for descending order.
SELECT Statement with GROUP BY Clause
- Used to create one output row per each group and produces summary values for the selected columns.
INSERT Statement
- Used to add new rows to a table.
- Can be used with a SELECT statement to insert rows from another table.
- Rules for INSERT statement:
- Inserting an empty string into a varchar or text column inserts a single space.
- All char columns are right-padded to the defined length.
- If an INSERT statement violates a constraint, default or rule, or if it is the wrong data type, the statement fails and SQL Server displays an error message.
UPDATE Statement
- Used to change data in existing rows.
- Can be used with a SELECT statement to modify data.
- Rules for UPDATE statement:
- Can be used with subqueries.
- Can be used to add new data or modify existing data.
DELETE Statement
- Used to remove rows from a table.
- Rules for DELETE statement:
- If you omit a WHERE clause, all rows in the table are removed.
- DELETE cannot be used with a view that has a FROM clause naming more than one table.
- DELETE can affect only one base table at a time.
- Three ways to complete deletion.
Data Manipulation Language (DML) Overview
- DML is used to query and modify database data
- DML includes four main statements: SELECT, INSERT, UPDATE, and DELETE
SELECT Statement
- Used to extract data from tables based on specific criteria
- Processed in the following sequence: SELECT, FROM, WHERE, GROUP BY, and ORDER BY
- Can be used with the following clauses:
- WHERE: specifies conditions for data extraction
- GROUP BY: groups data by one or more fields
- ORDER BY: sorts data in ascending or descending order
SELECT Statement Examples
- SELECT statement with WHERE clause: filters data based on conditions
- SELECT statement with ORDER BY clause: sorts data in alphabetical order
INSERT Statement
- Used to add new rows to a table
- Can be used with a SELECT statement to insert data from another table
- Rules for INSERT statement:
- Inserting an empty string into a varchar or text column inserts a single space
- Char columns are right-padded to the defined length
- Statement fails if it violates a constraint, default, or rule, or is the wrong data type
UPDATE Statement
- Used to modify existing data in a table
- Can be used with a subquery to update data based on conditions
- Example: updating job levels for employees hired in 2010
DELETE Statement
- Used to remove rows from a table
- Rules for DELETE statement:
- If no WHERE clause is specified, all rows in the table are removed
- DELETE cannot be used with a view that has a FROM clause naming more than one table
- DELETE can only affect one base table at a time
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
This quiz covers the basics of SQL, including the Data Manipulation Language (DML) and its applications. It introduces the four main SQL commands: SELECT, INSERT, UPDATE, and DELETE.
