SQL - Select Statement :
SELECT column1, column2, ... FROM table_name;
- SELECT: This keyword is used to specify the columns that you want to retrieve.
- column1, column2, ...: These are the columns you want to select data from.
- FROM: This keyword specifies the table from which you want to retrieve the data.
- table_name: This is the name of the table from which you want to retrieve the data.
Suppose we have a table named employees with columns employee_id, first_name, last_name, and salary. To retrieve the first_name and salary columns from the employees table, the SQL SELECT statement would look like this:
SELECT first_name, salary FROM employees;
Filtering Data : -
You can also filter data using the WHERE clause in the SELECT statement. For example, to retrieve only the employees with a salary greater than 50000:
Select first_name,salary from employees where salary>50000;
Aliasing : -
You can use aliases to give columns or tables temporary names. This can make your queries more readable. For example:-
SELECT first_name AS [FirstName], salary AS [Salary] FROM employees;
SQL Server - Listing Tables : -
we can use the "SELECT" statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database -
- sys.tables
- information_schema.tables
- sysobjects
The SYS.TABLES View
Following is the syntax to list down all the tables in SQL using the SYS.TABLES view -
SELECT * FROM SYS.TABLES;
Following is the output of the above query -
| name | object_id | principal_id | schema_id |
|---|---|---|---|
| CUSTOMERS | 4195065 | NULL | 1 |
| ORDER | 68195293 | NULL | 1 |
| COMPANY | 100195407 | NULL | 1 |
| TEACHERS | 2107154552 | NULL | 1 |
The INFORMATION_SCHEMA.TABLES View
Following is the syntax to list down all the tables in SQL using the INFORMATION_SCHEMA.TABLES view -
SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES;
Following is the output of the above query -
| table_name | table_type |
|---|---|
| CUSTOMERS | BASE TABLE |
| ORDERS | BASE TABLE |
| COMPANY | BASE TABLE |
| TEACHERS | BASE TABLE |
The SYSOBJECTS View : -
You can use SYSOBJECTS view to retrieve the information of all the objects created in SQL Server database, including stored procedures, views, system tables and user-defined tables. Following is the basic syntax of using sysobjects view -
SELECT name, id, xtype FROM sysobjects WHERE xtype = 'U';
This will produce following result :-
| name | id | xtype |
|---|---|---|
| CUSTOMERS | 4195065 | U |
| ORDER | 68195293 | U |
| COMPANY | 100195407 | U |
| TEACHERS | 2107154552 | U |
| Value | Meaning |
|---|---|
| AF | Aggregate function (CLR) |
| C | CHECK constraint |
| D | Default or DEFAULT constraint |
| F | FOREIGN KEY constraint |
| L | Log |
| FN | Scalar function |
| FS | Assembly (CLR) scalar-function |
| FT | Assembly (CLR) table-valued function |
| IF | In-lined table-function |
| IT | Internal table |
| P | Stored procedure |
| PC | Assembly (CLR) stored-procedure |
| PK | PRIMARY KEY constraint (type is K) |
| RF | Replication filter stored procedure |
| S | System table |
| SN | Synonym |
| SQ | Service queue |
| TA | Assembly (CLR) DML trigger |
| TF | Table function |
| TR | SQL DML Trigger |
| TT | Table type |
| U | User table |
| UQ | UNIQUE constraint (type is K) |
| V | View |
| X | Extended stored procedure |