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 |