SQL Column manipulations, Basic SELECT Statements, Column Aliases & Distinct Statement.
SQL basic SELECT Statements with example
In SQL, the SELECT statement is used to retrieve data from a database. It is one of the most fundamental and commonly used SQL commands. Here are some basic examples of how to use the SELECT statement:
Select all columns from a table:
SELECT * FROM table_name;
This query retrieves all columns from the specified table.
Select specific columns from a table:
SELECT column1, column2 FROM table_name;
This query retrieves only the specified columns from the table.
Select with alias names:
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;
This query renames the selected columns using aliases.
Select with conditions using WHERE clause:
SELECT * FROM table_name WHERE condition;
This query retrieves rows that satisfy the specified condition.
Select with ordering using ORDER BY clause:
SELECT * FROM table_name ORDER BY column_name;
This query sorts the result set in ascending order based on the specified column.
Select with limiting rows using LIMIT clause:
SELECT * FROM table_name LIMIT number_of_rows;
This query limits the number of rows returned by the SELECT statement.
Select with filtering using WHERE and ORDER BY clauses:
SELECT column1, column2 FROM table_name WHERE condition ORDER BY column_name;
This query combines filtering and ordering to retrieve specific rows from the table.
Select with aggregate functions:
SELECT COUNT(column_name) FROM table_name;
This query applies an aggregate function (such as COUNT, SUM, AVG, MIN, MAX) to the specified column.
Select with group by and having clause:
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;
This query groups rows based on the specified column and applies the aggregate function to each group, filtering the results using the HAVING clause.
These are some basic examples of using the SELECT statement in SQL.
Depending on the complexity of your data and the database management system you are using, you can combine these clauses and use more advanced functionalities to tailor your queries according to your specific needs.
SQL Column manipulations with example
Manipulating columns in SQL is a crucial aspect of data management. Here are some common column manipulation operations with examples:
Adding a new column to an existing table:
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE employees
ADD age INT;
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE employees
DROP COLUMN age;
Modifying the data type of a column:
ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;
ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;
Example:
ALTER TABLE employees
ALTER COLUMN salary DECIMAL(10,2);
Renaming a column in a table:
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
ALTER TABLE employees
RENAME COLUMN old_column_name TO new_column_name;
Adding a default value to a column:
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;
Dropping a default value from a column:
ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;
Adding a NOT NULL constraint to a column:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype NOT NULL;
Example:
ALTER TABLE employees
MODIFY COLUMN name VARCHAR(50) NOT NULL;
Dropping a NOT NULL constraint from a column:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype NULL;
ALTER TABLE table_name
MODIFY COLUMN column_name datatype NULL;
Example:
ALTER TABLE employees
MODIFY COLUMN name VARCHAR(50) NULL;
These are some common column manipulation operations that can be performed using SQL. Column manipulation is essential for modifying the structure of a table to ensure that it accurately represents the data it holds.
SQL Column Aliases with example
Column aliases are used in SQL to provide temporary names for columns or expressions in the result set.
They are especially useful for making the output more readable or for providing meaningful names to the result of calculations or operations. Here are some examples of using column aliases:
Simple column alias:
SELECT column_name AS alias_name FROM table_name;
Example:
SELECT first_name AS ‘First Name’, last_name AS ‘Last Name’ FROM employees;
Alias with mathematical operations:
SELECT column1 + column2 AS sum_result FROM table_name;
Example:
SELECT quantity * price AS total_cost FROM orders;
Alias with concatenation:
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM employees;
This will concatenate the first_name and last_name columns and display the result in the full_name column.
Alias with aggregate functions:
SELECT AVG(salary) AS average_salary FROM employees;
This will calculate the average of the salary column and display the result in the average_salary column.
Alias with expressions:
SELECT column1 * 0.15 AS tax_amount FROM table_name;
This will calculate 15% of column1 and display the result in the tax_amount column.
Alias with subqueries:
SELECT column1, (SELECT MAX(column2) FROM table2 WHERE table2.id = table1.id) AS max_value FROM table1;
Here, the subquery calculates the maximum value of column2 for each row in table1 and the result is displayed in the max_value column.
Column aliases make the output of SQL queries more readable and easier to understand, especially when dealing with complex queries involving calculations and subqueries.
SQL Distinct Statement with example
The DISTINCT keyword in SQL is used to retrieve unique or distinct values from a specific column or combination of columns in a table. Here’s an example of how to use the DISTINCT statement:
Suppose we have a table named students with the following data:
ID Name Course
1 John Mathematics
2 Jane Physics
3 John Chemistry
4 Sarah Mathematics
5 Jane Biology
Select distinct values from a single column:
SELECT DISTINCT Course FROM students;
This query will return distinct values from the Course column:
| Course |
|————–|
| Mathematics |
| Physics |
| Chemistry |
| Biology |
Select distinct values from multiple columns:
SELECT DISTINCT Name, Course FROM students;
This query will return distinct combinations of Name and Course:
| Name | Course |
|———-|————–|
| John | Mathematics |
| Jane | Physics |
| John | Chemistry |
| Sarah | Mathematics |
| Jane | Biology |
The DISTINCT statement is useful when you want to eliminate duplicate values from the result set and retrieve only unique values.