SQL Commands Overview

Questions and Answers

What is the primary function of the INSERT command?

To retrieve data from a database

To sort records in a specific order

To create a new database

To add new records to a table (correct)

Which command is used to remove records from a database?

DROP

DELETE (correct)

SELECT

UPDATE

When using the ORDER BY clause, what does the keyword ASC denote?

Sorting in ascending order (correct)

Sorting in descending order

Returning only distinct values

Filtering results based on a condition

What is the purpose of the WHERE clause in a SELECT statement?

To filter results by specific criteria Signup and view all the answers

Which SQL command would you use to create a new table in a database?

CREATE Signup and view all the answers

What does the INNER JOIN command do?

Joins records from two tables based on matching values Signup and view all the answers

What is the result of using the DROP command?

It drops an existing table or database Signup and view all the answers

In the context of SQL, what is the significance of using a datatype while creating a table?

To enforce data integrity and data type constraints Signup and view all the answers

What is the purpose of the INNER JOIN operation in SQL?

To combine rows from two or more tables based on a related column. Signup and view all the answers

What does the COUNT() function in SQL return?

The number of non-null values in a specified column. Signup and view all the answers

What will the SQL command 'SELECT AVG(price) FROM products;' return?

The average price of all products in the products table. Signup and view all the answers

In the SQL command 'SELECT MIN(price) FROM products;', what does MIN() function do?

Returns the minimum price found in the price column of products. Signup and view all the answers

What does the MAX() function indicate in an SQL query?

The maximum (highest) value in a specified column. Signup and view all the answers

Which SQL function would you use to find the total of a column's values?

SUM() Signup and view all the answers

Study Notes

SQL Commands Overview

SELECT: Retrieves data from a database.
- Syntax: SELECT column1, column2 FROM table_name;
- Example: SELECT first_name, last_name FROM customers;
INSERT: Adds new records to a table.
- Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- Example: INSERT INTO customers (first_name, last_name) VALUES ('Mary', 'Doe');
UPDATE: Modifies existing records in a table.
- Syntax: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
- Example: UPDATE employees SET employee_name = 'John Doe', department = 'Marketing';
DELETE: Removes records from a table.
- Syntax: DELETE FROM table_name WHERE condition;
- Example: DELETE FROM employees WHERE employee_name = 'John Doe';

Database Structure Commands

CREATE: Creates a new database or objects (e.g., tables, indices).
- Syntax: CREATE TABLE table_name (column1 datatype1, column2 datatype2);
- Example:
```
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);
```
DROP: Deletes an existing table from the database.
- Syntax: DROP TABLE table_name;
- Example: DROP TABLE customers;

Data Retrieval Clauses

WHERE Clause: Filters rows based on specified conditions.
- Syntax: SELECT * FROM table_name WHERE condition;
- Example: SELECT * FROM customers WHERE age > 30;
ORDER BY Clause: Sorts the result set in ascending or descending order based on a specified column.
- Syntax: SELECT * FROM table_name ORDER BY column_name ASC|DESC;
- Example: SELECT * FROM products ORDER BY price DESC;

Joining Tables

INNER JOIN: Returns rows with matching values in both tables.
- Syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
- Example: SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.id;

Aggregate Functions

COUNT(): Counts the number of rows or non-null values in a specified column.
- Syntax: SELECT COUNT(column_name) FROM table_name;
- Example: SELECT COUNT(age) FROM employees;
SUM(): Calculates the sum of all values in a specified column.
- Syntax: SELECT SUM(column_name) FROM table_name;
- Example: SELECT SUM(revenue) FROM sales;
AVG(): Calculates the average (mean) of all values in a specified column.
- Syntax: SELECT AVG(column_name) FROM table_name;
- Example: SELECT AVG(price) FROM products;
MIN(): Returns the minimum (lowest) value in a specified column.
- Syntax: SELECT MIN(column_name) FROM table_name;
- Example: SELECT MIN(price) FROM products;
MAX(): Returns the maximum (highest) value in a specified column.
- Syntax: SELECT MAX(column_name) FROM table_name;
- Example: SELECT MAX(price) FROM products;

Description

This quiz covers essential SQL commands such as SELECT, INSERT, and UPDATE. Each command is briefly described, along with syntax examples to illustrate their use in database management. Test your understanding of these fundamental SQL operations.