Basic Commands in SQL for Beginners
I am sharing some very important SQL Commands at the beginner level. These commands are very common and useful in everyday office work if you are using SQL. So, I am sharing different types of commands with syntax.
Basically, SQL is a Structured Query Language or you can say Standard Language for accessing and manipulating databases.
What we can do with SQL Commands?
We can execute queries against a database
We can retrieve, insert, update and delete data or records from a database
We can create new databases
We can create a new table in a database
We can create stored procedures in a database
We can create views in a database
We can set permissions on tables, procedures, and views
Select Query
We use the select query to retrieve selected columns or data from tables stored in a database.
1 | SELECT column_name FROM TABLE_NAME |
Distinct Query
This query is used to select unique records on the selected column from a table. We can also select all unique data from a table.
1 2 | SELECT DISTINCT column_name FROM TABLE_NAME |
1 2 | SELECT DISTINCT * FROM TABLE_NAME |
Where Query
This query is used to select data, a column on condition.
1 2 | SELECT column_name FROM TABLE_NAME WHERE condition |
AND/OR Condition
We use AND/OR when we want two conditions in Where Query. AND is used when we want both conditions true. OR is used when we want any condition to be true.
1 2 | SELECT column_name FROM TABLE_NAME WHERE condition_1 AND/OR condition_2 |
Create Table Query
We use this query to create a new table in the database. We give table names and column names based on our requirements.
1 2 3 4 | CREATE TABLE TABLE_NAME ( column_name_1 column_datatype (CONSTRAINT), column_name_2 column_datatype (CONSTRAINT) ) |
Insert Query
This query is used to insert records manually in an existing table.
1 2 | INSERT INTO TABLE_NAME (column_name_1, column_name_2, … ) VALUES ('value1', 'value2', … ) |
This query is used to insert records from one table to another table in a database.
1 2 | INSERT INTO table_name_1 (column_name_1, column_name_2) SELECT column_name_3, column_name_4 FROM table_name_2 |
Select Date Query
This query is used to select the current date and time from the system in which SQL is installed.
1 2 3 | SELECT GETDATE() --- Select the Current system date with the time SELECT CURDATE() --- Select Current system date SELECT CURTIME() --- Select Current system time |
Update Query
This query is used to update existing records in database tables. You can also update using criteria.
1 2 3 | UPDATE TABLE_NAME SET column_name = 'new_value' WHERE condition |
Delete Query
This query is used to delete records from an existing table in a database. You can delete all records and select records based on conditions.
1 2 | DELETE FROM TABLE_NAME WHERE condition |
Drop Query
This query is used to delete a table from the database. This will delete the table permanently.
1 | DROP TABLE TABLE_NAME |
Truncate Query
This query is used to delete all records in a table. This means this query will make the table empty.
1 | TRUNCATE TABLE TABLE_NAME |
Alter Table Query
Alter queries are basically used to change the structure of existing tables like changing variables, renaming columns, removing columns, the addition of columns, etc.
Add a column in a table
1 | ALTER TABLE TABLE_NAME ADD column_name (datatype) |
Alter or modify the column
1 | ALTER TABLE TABLE_NAME ALTER COLUMN column_name (new_datatype) |
Rename column
1 | ALTER TABLE TABLE_NAME RENAME COLUMN column_name_old TO column_name_new |
Drop column
1 | ALTER TABLE TABLE_NAME DROP COLUMN column_name |
Index
The index can be created on Tables for faster execution of queries or to fetch records faster.
There are two types of Index in SQL:
1. Unique Index
2. Non-Clustered Index
Create Index
This query is used to create an Index on columns in a table. This syntax will create a Non-Clustered index by default.
1 | CREATE INDEX index_name ON TABLE_NAME (column_name1, column_name2) |
Create Unique Index
This query is used to create an Index on columns in a table where records are unique.
1 | CREATE UNIQUE INDEX index_name ON TABLE_NAME (column_name1, column_name2) |
Add Index
This query is used to add an index to a table.
1 | ALTER TABLE TABLE TABLE_NAME ADD INDEX index_name |
Drop Index
This query is used to delete or drop existing indexes from a table.
1 | ALTER TABLE TABLE_NAME DROP INDEX index_name |
Add Constraint
This query is used to add criteria or conditions in records on a table
1 | ALTER TABLE TABLE_NAME ADD constraint_name constraint_type constraint_condition |
Drop Constraints
This query is used to delete or drop existing constraints from a table.
1 | ALTER TABLE TABLE_NAME DROP constraint_name |
Copy the table structure into a new one
This query is used to copy the structure of an existing table into another table. This will copy all column structures. So there are different syntaxes to do this task.
1 2 | SELECT TOP 0 * INTO new_table_name FROM old_table_name |
1 2 | SELECT * INTO new_table_name FROM old_table_name WHERE 1=2 |
1 2 | CREATE TABLE new_table_name AS SELECT * FROM old_table_name WHERE 0=1 |
Copy the table structure into a new one for selected columns
If you want to copy a few columns from an existing table then use this query.
1 2 3 | SELECT TOP 0 column_name1, column_name2, column_name3 INTO new_table_name FROM old_table_name |
Copy the structure with data into a new one
This query will copy the structure and all data from an existing table into the new table. You can write this query in different ways.
1 2 | SELECT * INTO new_table_name FROM old_table_name |
1 2 | SELECT * INTO new_table_name FROM old_table_name WHERE 1=1 |
1 2 | CREATE TABLE new_table_name AS SELECT * FROM old_table_name |
Copy data into an existing table
This query will copy all data in an existing table from another existing table but the structure of both tables should be the same.
1 2 | INSERT INTO new_table_name SELECT * FROM old_table_name |
Copy table from another database
If you want to copy a table from another existing database then run this query.
1 2 | SELECT * INTO new_table_name FROM database_name.old_table_name |
Functions in SQL
There are many different functions in SQL. Like finding max, min, first, count, etc. These functions are very useful for criteria-based data and for QA.
Max Function
This function is used in the query to get the maximum value in a particular column. Like maximum salary etc.
1 2 3 | SELECT MAX(column_name) FROM TABLE_NAME WHERE condition |
Min Function
This function is used in the query to get the minimum value in a particular column. Like minimum salary etc.
1 2 3 | SELECT MIN(column_name) FROM TABLE_NAME WHERE condition |
Average Function
This function is used in the query to get the average value of a particular column. Like average salary etc.
1 2 3 | SELECT AVG(column_name) FROM TABLE_NAME WHERE condition |
Count Function
This function is used in the query to get the count of rows or data of a particular column. Like you want to get the count of total employees or do you want to get employees to count under salary 50000 etc?
1 2 3 | SELECT COUNT(*) FROM TABLE_NAME WHERE condition |
1 2 3 | SELECT COUNT(column_name) FROM TABLE_NAME WHERE condition |
1 2 3 | SELECT DISTINCT COUNT(column_name) FROM TABLE_NAME WHERE condition |
Sum Function
This function is used in the query to get the sum or total of columns based on criteria. For example, you want to get the total salary given to a particular department’s employees.
1 2 3 | SELECT SUM(column_name) FROM TABLE_NAME WHERE condition |
Like Function
This function is used in the query to get similar data to a string or name from a particular column. If you want to fetch all records where the employee’s name is “Emilly”.
So it will search all records whose name contains Kumar. And there are some wildcards (criteria) to refine your search result.
1 2 3 | SELECT column_name FROM TABLE_NAME WHERE column_name LIKE '%Emilly%' |
Wildcards in Like Function
a% – Select all the records where the column value starts with a.
%a – Select all the records where the column value ends with a.
%emilly% – Select all the records whose name contains Emilly.
a_%_% – Select all the records where the column value start with a and the total length is 3 character.
a%w – Select all the records where the column value starts with a and ends with w.
In Function
This function is used in the query to enter multiple criteria values or clauses within a column.
1 2 3 | SELECT column_name FROM TABLE_NAME WHERE column_name IN ('value1', 'value2') |
1 2 3 | SELECT column_name FROM TABLE_NAME WHERE column_name IN (SELECT column_name FROM TABLE_NAME) -- combined two table result |
Between Function
This function is used in the query to get data between a range. Like from a particular date to another date, selecting salary data in the salary range, etc.
1 2 3 4 | SELECT column_name FROM TABLE_NAME WHERE column_name BETWEEN 'value1' AND 'value2' |
Order By Function
This function is used in the query to show results in ascending or descending order. By default, it takes ascending order but if you want to show results in descending order then use desc in the query.
1 2 3 | SELECT column_name FROM TABLE_NAME WHERE condition ORDER BY column_name [ASC/DESC] |
Group By Function
This function is used in the query when we use the aggregate function in the select query. And aggregate functions are Max, Min, Count, Average, Sum, etc.
1 2 3 | SELECT column_name1, SUM(column_name2) FROM TABLE_NAME GROUP BY column_name1 |
Union All Function
This function is used in the query to get the result from two or more tables. But the column names in both tables should be the same.
1 2 3 4 5 | SELECT column_name1, column_name2, column_name3 FROM table_name1 UNION ALL SELECT column_name1, column_name2, column_name3 FROM table_name2 |
0 Comments