SQL create table with Example
The SQL CREATE TABLE statement is used to create a new table in a database. You can define the structure of the table, including the columns and their data types. Here is the basic syntax of the CREATE TABLE statement:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
…
);
CREATE TABLE: This keyword is used to indicate that you want to create a new table.
table_name: This is the name of the table you want to create.
(column1, column2, column3, …): Inside the parentheses, you specify the columns of the table along with their data types. You can define multiple columns for the table.
datatype: This is the data type of the column, specifying the type of data that can be stored in that column (e.g., INT, VARCHAR, DATE, etc.).
Here’s an example of how to use the CREATE TABLE statement:
Suppose you want to create a table named customers to store information about customers. The table will have the following columns: customer_id, first_name, last_name, email, and birthdate. You can create this table using the following SQL statement:
CREATE TABLE customers (
customer_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
birthdate DATE
);
After running this query, you will have a new table named customers with the specified columns and data types.
You can also add additional constraints, such as PRIMARY KEY, UNIQUE, NOT NULL, or DEFAULT values, to further define the structure of the table. For example, you can make the customer_id column a primary key and set it to auto-increment:
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE,
birthdate DATE
);
In this example, we added the PRIMARY KEY constraint to the customer_id column to ensure it contains unique values, and we specified that it should auto-increment. We also added the UNIQUE constraint to the email column to ensure that each email address is unique.
The exact syntax for constraints may vary depending on the database system you’re using (e.g., MySQL, PostgreSQL, SQL Server), so be sure to refer to your specific database system’s documentation for details.
SQL Update statement with example
An SQL UPDATE statement is used to modify existing records in a database table. The basic syntax of an UPDATE statement is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
UPDATE: This keyword is used to indicate that you want to update records in the specified table.
table_name: This is the name of the table you want to update.
SET: This keyword is used to specify the columns that you want to update and the new values you want to set for them.
column1 = value1, column2 = value2, …: Here, you list the columns you want to update and the new values you want to assign to them. You can update one or more columns at once.
WHERE: This clause is optional but very important. It allows you to specify a condition that determines which records will be updated. If you omit the WHERE clause, all records in the table will be updated.
condition: This is the condition that specifies which records should be updated. It is usually expressed as an SQL expression using comparison operators (e.g., =, <, >, AND, OR, etc.).
Here’s an example of how to use the UPDATE statement:
Let’s assume you have a table named employees with the following structure:
| employee_id | first_name | last_name | salary |
|————|————|———–|——–|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Bob | Johnson | 55000 |
Suppose you want to update the salary of employee with employee_id 2 to $65000. You can do it with the following SQL UPDATE statement:
UPDATE employees
SET salary = 65000
WHERE employee_id = 2;
After running this query, the employees table will be updated as follows:
| employee_id | first_name | last_name | salary |
|————|————|———–|——–|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 65000 | <– Updated
| 3 | Bob | Johnson | 55000 |
This is a simple example, but you can use the UPDATE statement to make more complex updates based on your specific needs. Just remember to be cautious with the WHERE clause to avoid unintended updates to your data.
SQL insert into statement with Example
The SQL INSERT INTO statement is used to add new records (rows) into a database table. The basic syntax of an INSERT INTO statement is as follows:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
INSERT INTO: This keyword is used to indicate that you want to insert new records into the specified table.
table_name: This is the name of the table into which you want to insert the new records.
(column1, column2, column3, …): This part is optional but can be used to specify the columns in the table where you want to insert data.
If you omit this part, you need to provide values for all columns in the order they appear in the table.
VALUES (value1, value2, value3, …): This part is used to specify the values that you want to insert into the specified columns. The values should be in the same order as the columns.
Here’s an example of how to use the INSERT INTO statement:
Let’s assume you have a table named employees with the following structure:
| employee_id | first_name | last_name | salary |
|————|————|———–|——–|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Bob | Johnson | 55000 |
Now, suppose you want to insert a new employee into the employees table. You can do it with the following SQL INSERT INTO statement:
INSERT INTO employees (first_name, last_name, salary)
VALUES (‘Alice’, ‘Williams’, 70000);
After running this query, a new record will be added to the employees table:
| employee_id | first_name | last_name | salary |
|————|————|———–|——–|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Bob | Johnson | 55000 |
| 4 | Alice | Williams | 70000
<– New record
In this example, we specified the columns we wanted to insert data into (first_name, last_name, and salary) and provided the corresponding values for those columns.
You can also insert multiple records in a single INSERT INTO statement by separating the sets of values with commas:
INSERT INTO employees (first_name, last_name, salary)
VALUES (‘Eve’, ‘Davis’, 60000), (‘Michael’, ‘Brown’, 75000);
This will insert two new records into the employees table in one query.
SQL delete statement with Example
The SQL DELETE statement is used to remove one or more records (rows) from a database table. The basic syntax of a DELETE statement is as follows:
DELETE FROM table_name
WHERE condition;
DELETE FROM: This keyword is used to indicate that you want to delete records from the specified table.
table_name: This is the name of the table from which you want to delete records.
WHERE: This clause is used to specify a condition that determines which records should be deleted. If you omit the WHERE clause, all records in the table will be deleted.
condition: This is the condition that specifies which records should be deleted. It is usually expressed as an SQL expression using comparison operators (e.g., =, <, >, AND, OR, etc.).
Here’s an example of how to use the DELETE statement:
Let’s assume you have a table named employees with the following structure:
| employee_id | first_name | last_name | salary |
|————|————|———–|——–|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Bob | Johnson | 55000 |
| 4 | Alice | Williams | 70000 |
Now, suppose you want to delete an employee with employee_id 2 from the employees table. You can do it with the following SQL DELETE statement:
DELETE FROM employees
WHERE employee_id = 2;
After running this query, the employees table will be updated as follows, with employee 2 removed:
| employee_id | first_name | last_name | salary |
|————|————|———–|——–|
| 1 | John | Doe | 50000 |
| 3 | Bob | Johnson | 55000 |
| 4 | Alice | Williams | 70000 |
In this example, we used the WHERE clause to specify the condition for deletion, which in this case was to delete the record with employee_id equal to 2. The DELETE statement removed that specific record from the table.
Be cautious when using the DELETE statement, as it can permanently remove data from your database. Always double-check your condition to ensure you’re deleting the correct records.
SQL group by Statement with Example
The SQL GROUP BY statement is used to group rows that have the same values in specified columns into summary rows, typically for the purpose of applying aggregate functions to those groups.
It’s a useful way to perform calculations on groups of data within a table. The basic syntax of the GROUP BY statement is as follows:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;
SELECT: This keyword is used to select the columns that you want to include in the result set.
column1, column2: These are the columns that you want to group by and include in the result set. You can group by one or more columns.
aggregate_function(column3): You can use aggregate functions like SUM, COUNT, AVG, MAX, MIN, etc., to perform calculations on the grouped data.
FROM table_name: This specifies the name of the table from which you are selecting data.
GROUP BY column1, column2: This clause is used to group the data based on the specified columns.
Here’s an example of how to use the GROUP BY statement:
Let’s assume you have a table named orders with the following structure:
| order_id | customer_id | product_id | quantity |
|———|————|————|———-|
| 1 | 101 | 1 | 5 |
| 2 | 101 | 2 | 3 |
| 3 | 102 | 1 | 2 |
| 4 | 103 | 2 | 4 |
| 5 | 103 | 3 | 1 |
Suppose you want to find the total quantity of products ordered by each customer. You can use the GROUP BY statement with the SUM function to accomplish this:
SELECT customer_id, SUM(quantity) as total_quantity
FROM orders
GROUP BY customer_id;
After running this query, you will get a result set that shows the total quantity of products ordered by each customer:
| customer_id | total_quantity |
|————|—————-|
| 101 | 8 |
| 102 | 2 |
| 103 | 5 |
In this example, the data has been grouped by the customer_id, and the SUM function is applied to the quantity column within each group. This gives you the total quantity of products ordered by each customer.
You can use other aggregate functions or apply more complex queries when using GROUP BY to analyze your data in different ways.
SQL order by statement with Example
The SQL ORDER BY statement is used to sort the result set of a SELECT query in a specified order. You can use ORDER BY to sort the data in ascending (default) or descending order based on one or more columns. Here’s the basic syntax of the ORDER BY statement:
SELECT column1, column2, …
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;
SELECT: This keyword is used to specify the columns you want to include in the result set.
column1, column2, …: These are the columns you want to retrieve from the table.
FROM table_name: This part specifies the table from which you are selecting data.
ORDER BY: This keyword indicates that you want to sort the result set.
column1, column2, …: These are the columns by which you want to sort the data. You can specify one or more columns for sorting.
[ASC|DESC]: You can specify the sort order. ASC (ascending) is the default order, and DESC (descending) is used to sort in reverse order.
Here’s an example of how to use the ORDER BY statement:
Let’s assume you have a table named employees with the following structure:
| employee_id | first_name | last_name | salary |
|————|————|———–|——–|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Bob | Johnson | 55000 |
| 4 | Alice | Williams | 70000 |
Suppose you want to retrieve a list of employees sorted by their salary in descending order. You can use the ORDER BY statement like this:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
After running this query, you will get a result set with employees sorted by salary in descending order:
| first_name | last_name | salary |
|————|———–|——–|
| Alice | Williams | 70000 |
| Jane | Smith | 60000 |
| Bob | Johnson | 55000 |
| John | Doe | 50000 |
In this example, the data is sorted by the salary column in descending order, so the employees with the highest salary appear first in the result set.
You can also sort by multiple columns by specifying them in the ORDER BY clause. For example, you can sort employees by last_name in ascending order and then by first_name in ascending order like this:
SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC, first_name ASC;
This would sort the employees alphabetically by last name and then by first name within each last name group