What is Delete Statement in SQL with example
In SQL, the DELETE statement is used to remove one or more rows from a table. It allows you to delete specific records based on certain conditions, or you can delete all records from a table without any conditions. It’s important to be cautious when using the DELETE statement, as it can permanently remove data from your database. Therefore, it’s often a good practice to back up your data before performing any delete operations.
Here is the basic syntax for the DELETE statement:
DELETE FROM table_name
WHERE condition;
table_name is the name of the table from which you want to delete data.
condition is an optional parameter that specifies the criteria for the rows to be deleted. If the condition is not specified, all rows in the table will be deleted.
Here’s an example:
Suppose you have a table named “customers” with the following structure:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
city VARCHAR(50),
email VARCHAR(50)
);
INSERT INTO customers (customer_id, customer_name, city, email)
VALUES (1, ‘John Doe’, ‘New York’, ‘john.doe@example.com’),
(2, ‘Jane Smith’, ‘Los Angeles’, ‘jane.smith@example.com’),
(3, ‘Bob Johnson’, ‘Chicago’, ‘bob.johnson@example.com’);
To delete a specific record from the “customers” table, you can use the DELETE statement as follows:
DELETE FROM customers
WHERE customer_id = 2;
This will delete the customer with the customer_id 2 from the “customers” table. If you want to delete all records from the table, you can simply run:
DELETE FROM customers;
This will delete all the records in the “customers” table. However, be cautious when using this statement, as it will delete all data in the table without any conditions. Always make sure to back up your data before performing such operations.
SQL How to use in SQL Select Top with example
In SQL, the SELECT TOP statement is used to retrieve a specified number of rows from the top of a result set. It is commonly used in scenarios where you want to retrieve a specific number of records from a table based on a certain criteria, such as the highest or lowest values in a column. The syntax for the SELECT TOP statement can vary depending on the database system being used.
Here’s the basic syntax for the SELECT TOP statement:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
number is the number of rows to be returned from the top of the result set. It can be an integer value.
percent is the percentage of rows to be returned from the top of the result set. It must be a value between 0 and 100.
Here’s an example using the SQL Server syntax:
Suppose you have a table named “students” with the following structure:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
score INT
);
INSERT INTO students (student_id, student_name, score)
VALUES (1, ‘John Doe’, 85),
(2, ‘Jane Smith’, 92),
(3, ‘Bob Johnson’, 78),
(4, ‘Mary Williams’, 96),
(5, ‘David Brown’, 88);
To retrieve the top 3 students based on their scores, you can use the following SQL query:
SELECT TOP 3 student_name, score
FROM students
ORDER BY score DESC;
This will return the names and scores of the top 3 students, sorted in descending order based on their scores.
In other database systems like MySQL and PostgreSQL, the equivalent of the TOP keyword is the LIMIT clause. For example, in MySQL, you would use the following query:
SELECT student_name, score
FROM students
ORDER BY score DESC
LIMIT 3;
This would achieve the same result as the previous SQL Server query, retrieving the top 3 students based on their scores.
SQL Where use Min and Max in SQL with example
In SQL, the MIN and MAX functions are used to find the minimum and maximum values, respectively, from a specific column in a table. These functions are particularly useful when you want to retrieve the smallest or largest value from a set of data. Here’s how you can use the MIN and MAX functions in SQL, along with an example:
MIN function:
The MIN function is used to find the minimum value in a specific column.
SELECT MIN(column_name) FROM table_name;
MAX function:
The MAX function is used to find the maximum value in a specific column.
SELECT MAX(column_name) FROM table_name;
Here’s an example using a table named “products”:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price DECIMAL(10, 2)
);
INSERT INTO products (product_id, product_name, price)
VALUES
(1, ‘Laptop’, 1200.00),
(2, ‘Mouse’, 25.00),
(3, ‘Keyboard’, 50.00),
(4, ‘Monitor’, 300.00),
(5, ‘Headphones’, 80.00);
Using the MIN and MAX functions:
SELECT MIN(price) AS min_price FROM products;
SELECT MAX(price) AS max_price FROM products;
This will give you the minimum and maximum prices from the “products” table.
The output will be like this:
min_price
25.00
max_price
1200.00
In this example, the MIN function retrieves the minimum price from the “products” table, while the MAX function retrieves the maximum price.