2.1. Database Usage and SQL DML Statements

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the purpose of the SQL SELECT statement?

To update existing records in a table

To delete records from a table

To retrieve data from one or more tables (correct)

To insert new records into a table

What does the WHERE clause do in an SQL statement?

It groups rows that have the same values

It specifies the order of the results

It filters records based on a specified condition (correct)

It retrieves all columns from a table

In the SQL INSERT statement, what does the VALUES clause specify?

The order in which the data will be displayed

The table from which the data is retrieved

The actual data to be inserted into the specified columns (correct)

The specific columns to be updated

Which SQL command will modify existing records in a table?

UPDATE Signup and view all the answers

What will happen if the WHERE clause is omitted in an UPDATE statement?

All records in the table will be updated Signup and view all the answers

Which of the following is a valid example of a SQL SELECT statement?

SELECT VARDAS, ATLYGINIMAS FROM DARBUOTOJAI Signup and view all the answers

In an SQL DELETE statement, what does the WHERE clause determine?

Which records are to be deleted Signup and view all the answers

What does the GROUP BY clause do in an SQL query?

It combines rows that have the same values in specified columns Signup and view all the answers

What will happen if the DELETE statement does not include a WHERE clause?

All records in the table will be deleted. Signup and view all the answers

Which of the following SQL statements correctly selects employees with salaries between 500 and 700?

SELECT NAME FROM EMPLOYEES WHERE SALARY BETWEEN 500 AND 700; Signup and view all the answers

What does the SQL WHERE clause allow you to do?

Filter records based on specified conditions. Signup and view all the answers

Which symbol can be used in a SQL statement to match any single character?

_ Signup and view all the answers

What will the statement 'SELECT CODE FROM EMPLOYEES WHERE NAME IN ('John', 'Doe')' return?

Employee codes for John and Doe only. Signup and view all the answers

How would you update an employee's surname in the EMPLOYEES table where the first name is 'John'?

UPDATE EMPLOYEES SET SURNAME = 'Smith' WHERE FIRST_NAME = 'John'; Signup and view all the answers

What does the SQL statement 'DROP TABLE employees' do?

Removes the employees table and all its records permanently. Signup and view all the answers

Which SQL command would you use to find all employees who do not have a store ID assigned?

SELECT * FROM EMPLOYEES WHERE STORE_ID IS NULL; Signup and view all the answers

Study Notes

Database Usage, SQL

SQL is used for manipulating and retrieving data from databases.
DML (Data Manipulation Language) statements are used to manage data within a database.

DML Statements

SELECT: Retrieves data from one or more tables.
INSERT: Adds new rows into a table.
UPDATE: Modifies existing rows in a table.
DELETE: Removes existing rows from a table.
FROM: Specifies the table(s) from which to retrieve data.
WHERE: Filters data based on a specified condition.
GROUP BY: Groups rows with the same values in a column(s).
ORDER BY: Sorts the results based on one or more columns.
HAVING: Filters grouped data based on a condition.

SQL SELECT Statement

Used to retrieve data from a table or tables.
The result is a result-set, which is a table form.
Syntax:
- SELECT column1, column2, ... FROM table_name;
- SELECT * FROM table_name; (* means all columns*)

SQL WHERE Clause

Filters data based on a condition.
Syntax:
- SELECT column1, column2, ... FROM table_name WHERE condition;

SQL INSERT Statement

Used to add new rows to a table.
Syntax:
- INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

SQL UPDATE Statement

Used to modify existing rows in a table.
Syntax:
- UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

SQL DELETE Statement

Used to remove rows from a table.
Syntax:
- DELETE FROM table_name WHERE condition;

SQL WHERE Clause (Operators)

BETWEEN: Matches values within a specified range.
IN: Matches values in a list.
LIKE: Matches patterns. % matches any sequence of characters, _ matches any single character.
NOT: Negates a condition.
AND: Combines conditions that must both be true
OR: Combines conditions where at least one must be true
IS NULL: Checks for null values.

SQL GROUP BY

Groups rows that have similar values in specified columns.
Used with aggregate functions.
Aggregate Functions:
- AVG(): Calculates the average.
- COUNT(): Counts the number of rows.
- MAX(): Finds the maximum value.
- MIN(): Finds the minimum value.
- SUM(): Calculates the sum

SQL HAVING Clause

Filters grouped data based on a condition.
Used after a GROUP BY clause.

SQL Additional functions

DISTINCT: Retrieves only unique values.
LOWER: Converts strings to lowercase.
UPPER: Converts strings to uppercase.
CONCAT: Concatenates strings.

SQL Syntax summary

General syntax: SELECT columns FROM table WHERE condition GROUP BY grouping_columns HAVING condition ORDER BY ordering_columns

Studying That Suits You

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

Description

This quiz covers the manipulation and retrieval of data using SQL, focusing on Data Manipulation Language (DML) statements. Participants will learn about key commands such as SELECT, INSERT, UPDATE, and DELETE, along with filtering and sorting options to manage database queries effectively.