Major Commands in SQL with Examples
To illustrate the major SQL commands, let’s use a SQLite database file named `company.db`, which contains a table named `employees`. We’ll demonstrate various SQL commands with real changes to this database.
Example Database Structure
Table: employees
employee_id |
name |
age |
department |
---|---|---|---|
1 |
John Doe |
30 |
HR |
2 |
Jane Smith |
35 |
Finance |
3 |
Michael Lee |
40 |
IT |
SELECT Statement
The SELECT statement is used to retrieve data from one or more tables in a database.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example
SELECT * FROM employees WHERE department = 'IT';
This query selects all columns from the “employees” table where the department is ‘IT’.
Output
3|Michael Lee|40|IT
INSERT Statement
The INSERT statement is used to add new records into a table.
Syntax
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Example
INSERT INTO employees (name, age, department)
VALUES ('Sarah Johnson', 28, 'Marketing');
This query inserts a new employee record into the “employees” table with specified values.
Output
To see, if the new data has been successfully inserted, you can execute the SELECT command, like this
SELECT * FROM employees;
Now, you’ll get the entire table and you can see that the new data has been added to the database
1|John Doe|30|HR
2|Jane Smith|35|Finance
3|Michael Lee|40|IT
4|Sarah Johnson|28|Marketing
UPDATE Statement
The UPDATE statement is used to modify existing records in a table.
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example
UPDATE employees
SET department = 'Operations'
WHERE name = 'Michael Lee';
This query updates the department of the employee named ‘Michael Lee’ to ‘Operations’.
Output
Let’s run the SELECT command to see the updated database
SELECT * FROM employees;
You can see that, the database has been updated and now Michael’s department is set to Operations
1|John Doe|30|HR
2|Jane Smith|35|Finance
3|Michael Lee|40|Operations
4|Sarah Johnson|28|Marketing
DELETE Statement
The DELETE statement is used to remove existing records from a table.
Syntax
DELETE FROM table_name
WHERE condition;
Example
DELETE FROM employees
WHERE age > 35;
This query deletes records from the “employees” table where the age is greater than 35.
Output
Execute the SELECT command to check the updated database:
SELECT * FROM employees;
You can see that Michael has been removed from the database as he is the only one with an age over 35.
1|John Doe|30|HR
2|Jane Smith|35|Finance
4|Sarah Johnson|28|Marketing
What is a Query in DBMS?
In the field of Database Management Systems (DBMS), a query serves as a fundamental tool for retrieving, manipulating, and managing data stored within a database. Queries act as the bridge between users and databases, enabling them to communicate with the system to extract specific information or perform various operations on the data. Understanding the nuances of queries and their associated terminologies is crucial for anyone working with databases, whether as a developer, analyst, or database administrator.