Structured Query Language (SQL) is a programming language used to manage and manipulate relational databases. SQL is a powerful tool that allows users to interact with databases and perform a wide range of tasks, from creating tables and inserting data to querying, updating, and deleting data. In this article, we will discuss some of the most common SQL commands and how to use them.
SELECT: The SELECT statement is used to retrieve data from one or more tables. It is the most commonly used SQL command and allows you to specify the columns you want to retrieve, the tables you want to retrieve from, and any conditions that must be met.
For example, to retrieve all the data from a table called “customers”, you would use the following command:
SELECT * FROM customers;
This command will retrieve all the columns and rows from the “customers” table.
INSERT: The INSERT statement is used to insert new data into a table. It allows you to specify the table you want to insert into, the columns you want to insert data into, and the values you want to insert.
For example, to insert a new customer into the “customers” table, you would use the following command:
INSERT INTO customers (first_name, last_name, email) VALUES (‘John’, ‘Doe’, ‘johndoe@email.com’);
This command will insert a new customer with the first name “John”, last name “Doe”, and email address “johndoe@email.com” into the “customers” table.
UPDATE: The UPDATE statement is used to update existing data in a table. It allows you to specify the table you want to update, the columns you want to update, and the new values you want to set.
For example, to update the email address of a customer with the ID of 1 in the “customers” table, you would use the following command:
UPDATE customers SET email = ‘newemail@email.com’ WHERE id = 1;
This command will update the email address of the customer with the ID of 1 to “newemail@email.com”.
DELETE: The DELETE statement is used to delete data from a table. It allows you to specify the table you want to delete from and any conditions that must be met.
For example, to delete a customer with the ID of 1 from the “customers” table, you would use the following command:
DELETE FROM customers WHERE id = 1;
This command will delete the customer with the ID of 1 from the “customers” table.
CREATE: The CREATE statement is used to create a new table in a database. It allows you to specify the name of the table, the columns you want to create, and any constraints or indexes that should be applied.
For example, to create a new table called “orders” with columns for the order ID, customer ID, and order date, you would use the following command:
CREATE TABLE orders (
id INT NOT NULL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
This command will create a new table called “orders” with columns for the order ID, customer ID, and order date. It also sets the order ID column as the primary key and creates a foreign key constraint to link the customer ID column to the “customers” table.
ALTER: The ALTER statement is used to modify the structure of an existing table. It allows you to add or delete columns, change the data type of columns, or modify constraints or indexes.
For example, to add a new column called “phone” to the “customers” table, you would use the following command:
ALTER TABLE customers ADD phone VARCHAR(20);
This command will add a new column called “phone” with a data type of VARCHAR(20) to the “customers” table.
GROUP BY: The GROUP BY statement is used to group the result-set by one or more columns. It allows you to perform aggregate functions (such as SUM, COUNT, AVG) on the grouped data.
For example, to retrieve the total sales for each product category in the “sales” table, you would use the following command:
SELECT product_category, SUM(sales_amount) FROM sales GROUP BY product_category;
This command will group the sales data by product category and calculate the total sales amount for each category.
JOIN: The JOIN statement is used to combine rows from two or more tables based on a related column between them. There are several types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
For example, to retrieve the order details for each customer in the “customers” and “orders” tables, you would use the following command:
SELECT * FROM customers INNER JOIN orders ON customers.id = orders.customer_id;
This command will join the “customers” and “orders” tables based on the customer ID column and retrieve all the columns from both tables.
LIKE: The LIKE operator is used to search for a pattern in a column. It allows you to use wildcards (such as % or _) to match any characters or a single character.
For example, to retrieve all the customers with a last name starting with “S” in the “customers” table, you would use the following command:
SELECT * FROM customers WHERE last_name LIKE ‘S%’;
This command will retrieve all the rows from the “customers” table where the last name starts with the letter “S”.
Conclusion: SQL is a versatile language with a wide range of commands that allow you to manage and manipulate relational databases. By mastering these common SQL commands, you can efficiently perform tasks such as retrieving data, inserting new data, updating existing data, and modifying table structures.
If you’re looking to enhance your understanding of SQL, LearnTube offers an array of online courses to suit your needs. LearnTube provides a comprehensive learning experience through its dedicated learning app and WhatsApp bot. Whether you’re a beginner or an experienced learner, our platform offers a wide range of courses to cater to your needs. Browse our extensive selection of courses on our website to gain valuable insights.
