• 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 .

sql joins

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 :

NameDescription
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.

 

  1. SQL SELECT
  2. Types of SQL Statements
  3. SQL Syntax
  4. Free Books