- You are here:
-
Home
- SQL
SELECT table_catalog, table_schema, table_name,*
FROM information_schema.tables
WHERE ( table_name) NOT IN
(SELECT table_name
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY')
AND table_schema NOT IN ('information_schema', 'pg_catalog')
and TABLE_TYPE <> 'VIEW'
Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.
Following are commonly used constraints available in SQL. These constraints have already been discussed in SQL - RDBMS Concepts chapter but its worth to revise them at this point.
-
NOT NULL Constraint: Ensures that a column cannot have NULL value.
-
DEFAULT Constraint: Provides a default value for a column when none is specified.
-
UNIQUE Constraint: Ensures that all values in a column are different.
-
PRIMARY Key: Uniquely identified each rows/records in a database table.
-
FOREIGN Key: Uniquely identified a rows/records in any another database table.
-
CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
-
INDEX: Use to create and retrieve data from the database very quickly.
Constraints can be specified when a table is created with the CREATE TABLE statement or you can use ALTER TABLE statement to create constraints even after the table is created.
Dropping Constraints:
Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.
For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command:
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint and then enable it later.
Integrity Constraints:
Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.
There are many types of integrity constraints that play a role in referential integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints mentioned above.
Source https://www.tutorialspoint.com/sql/sql-constraints.htm
SQL data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL.
You would use these data types while creating your tables. You would choose a particular data type for a table column based on your requirement.
SQL Server offers six categories of data types for your use −
Exact Numeric Data Types
| DATA TYPE | FROM | TO |
|---|---|---|
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| int | -2,147,483,648 | 2,147,483,647 |
| smallint | -32,768 | 32,767 |
| tinyint | 0 | 255 |
| bit | 0 | 1 |
| decimal | -10^38 +1 | 10^38 -1 |
| numeric | -10^38 +1 | 10^38 -1 |
| money | -922,337,203,685,477.5808 | +922,337,203,685,477.5807 |
| smallmoney | -214,748.3648 | +214,748.3647 |
Approximate Numeric Data Types
| DATA TYPE | FROM | TO |
|---|---|---|
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
Date and Time Data Types
| DATA TYPE | FROM | TO |
|---|---|---|
| datetime | Jan 1, 1753 | Dec 31, 9999 |
| smalldatetime | Jan 1, 1900 | Jun 6, 2079 |
| date | Stores a date like June 30, 1991 | |
| time | Stores a time of day like 12:30 P.M. | |
Note − Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.
Character Strings Data Types
| DATA TYPE | Description |
|---|---|
| char | Maximum length of 8,000 characters.( Fixed length non-Unicode characters) |
| varchar | Maximum of 8,000 characters.(Variable-length non-Unicode data). |
| varchar(max) | Maximum length of 231characters, Variable-length non-Unicode data (SQL Server 2005 only). |
| text | Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters. |
Unicode Character Strings Data Types
| DATA TYPE | Description |
|---|---|
| nchar | Maximum length of 4,000 characters.( Fixed length Unicode) |
| nvarchar | Maximum length of 4,000 characters.(Variable length Unicode) |
| nvarchar(max) | Maximum length of 231characters (SQL Server 2005 only).( Variable length Unicode) |
| ntext | Maximum length of 1,073,741,823 characters. ( Variable length Unicode ) |
Binary Data Types
| DATA TYPE | Description |
|---|---|
| binary | Maximum length of 8,000 bytes(Fixed-length binary data ) |
| varbinary | Maximum length of 8,000 bytes.(Variable length binary data) |
| varbinary(max) | Maximum length of 231 bytes (SQL Server 2005 only). ( Variable length Binary data) |
| image | Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data) |
Misc Data Types
| DATA TYPE | Description |
|---|---|
| sql_variant | Stores values of various SQL Server-supported data types, except text, ntext, and timestamp. |
| timestamp | Stores a database-wide unique number that gets updated every time a row gets updated |
| uniqueidentifier | Stores a globally unique identifier (GUID) |
| xml | Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only). |
| cursor | Reference to a cursor object |
| table | Stores a result set for later processing |
Source :
http://www.w3schools.com/sqL/sql_datatypes_general.asp
https://www.tutorialspoint.com/sql/sql-data-types.htm
Different types of JOINs
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables
- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
- FULL JOIN: Return all rows when there is a match in ONE of the tables
- SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
- CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables .
For more about sql join
http://www.w3schools.com/sql/sql_join_inner.asp
http://www.sql-join.com/sql-join-types
https://www.tutorialspoint.com/sql/sql-inner-joins.htm
SQL SELECT DISTINCT Statement
Redundancy is the repetition of certain data in a table. With the use of DISTINCT clause data redundancy may be avoided. This clause will eliminate the repetitive appearance of same data. DISTINCT can come only once in a given select statement.
Syntax :
SELECT DISTINCT <column_name> FROM <table_name> WHERE <conditions>;
Parameters :
| Name | Description |
|---|---|
| column_name | Name of the column. |
| table_name | Name of the table. |
| conditions | It may be a condition, a select query or an expression. |
- SELECT DISTINCT returns only distinct (different) values.
- SELECT DISTINCT eliminates duplicate records from the results.
- DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.
- DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.