SQL
HOW TO USE NOT NULL CONSTRAINT IN SQL?
The NOT NULL constraint in SQL is used to disallow the acceptance of NULL value in a column(s). A column, by default, can hold NULL values. Example of NOT NULL constraint in SQL server- CREATE TABLE Students ( ID INT NOT NULL, FirstName VARCHAR(200) NOT NULL, Age INT); NOT NULL constraint creation on existing table- ALTER TABLE Students ALTER COLUMN Age INT…
HOW TO USE UNIQUE CONSTRAINT IN SQL?
The UNIQUE constraint in SQL is used to ensure that all values in a column are unique or different. Example of UNIQUE constraint in SQL server- CREATE TABLE Students (ID INT NOT NULL UNIQUE, FirstName VARCHAR(200),Fees INT); Naming of UNIQUE constraint in SQL server during creation of table- CREATE TABLE Students (ID INT NOT NULL, FirstName VARCHAR(200) NOT NULL, Fees INT , Age INT , CONSTRAINT unk UNIQUE (ID, FirstName)); –constraint named as unk…
WHAT ARE SQL ALIASES?
The aliases in SQL are used to give temporary name to a table or a column in a table. An alias is created with the AS keyword. Syntax of alias in SQL for the column- SELECT <column_name1> AS <alias_name1>, …. <column_nameN> AS <alias_nameN> FROM <table_name>; Syntax of alias in SQL for table- SELECT<column_name(s)> FROM <table_name> AS <alias_name>; Example of alias in SQL for column- SELECT CustomerName AS Customer, PostalCode AS [Postal Code]…
HOW TO USE DEFAULT CONSTRAINT IN SQL?
The DEFAULT constraint in SQL is used to set a default value for a column. Example of DEFAULT constraint creation in SQL server- CREATE TABLE Students ( ID INT NOT NULL UNIQUE, FirstName VARCHAR(200), Fees INT, City VARCHAR(200) DEFAULT ‘Delhi’ ); Example to create DEFAULT constraint on existing table in SQL server- ALTER TABLE Students ADD CONSTRAINT d_city DEFAULT ‘Delhi’ FOR City; Example to DROP a UNIQUE constraint in SQL server –…
HOW TO CREATE INDEX IN SQL?
The CREATE INDEX statement in SQL is used to create indexes in tables. Indexes are used for faster retrieval of records which speed up queries. Syntax of CREATE INDEX in SQL server. It allow duplicate values CREATE INDEX <index_name> ON <table_name> (column_name1..column_nameN); Syntax of CREATE UNIQUE INDEX in SQL server. It don’t allow duplicate values CREATE UNIQUE INDEX <index_name> ON <table_name> (column_name1..column_nameN); Example of…
HOW TO USE UNION IN SQL?
UNION operator is used to select distinct values from the combination of the result-set of two or more SELECT statements, provided that the data type and the number of fields, and the order of fields must be the same for every SELECT statement. To allow duplicate values in the result set, use UNION ALL. Syntax of UNION…
WHAT IS WINDOW FUNCTION IN SQL SERVER?
Window function is a function which uses values from one or multiple rows to return a value for each row. The term Window describes the set of rows in the database on which the function will operate using an OVER() clause. The main advantage of using Window functions over regular aggregate functions is- Window functions…
WHAT IS CTE OR COMMON TABLE EXPRESSION IN SQL SERVER?
CTE or Common Table Expression, is a named temporary result set that we can define and reference within the execution scope of single SELECT, INSERT, UPDATE, DELETE, MERGE or CREATE VIEW statement.A CTE is similar to a view, but it is defined within the execution of a single statement and lasts only for the duration…
WHAT IS PIVOT AND UNPIVOT IN SQL SERVER?
PIVOT and UNPIVOT are relational operators. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they’re required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT…
SQL FREE COURSE
Learn SQL