SQL Individual Training - 2

 like 

 % percent wildcard matches zero, one, or more characters

 _ underscore wildcard matches a single character.

SELECT * FROM employees1 WHERE first_name LIKE 'Da%';

SELECT

 employee_id, first_name, last_name 

FROM

 employees1 

WHERE

 last_name 

LIKE

 '%an%';

PRE- Defined Functins 

SELECT UPPER('Allah-hus-samad');

SELECT REVERSE('abcd');

SELECT LEN('text');

SELECT ROUND(157.96,0)

select CAST(ROUND(10.255,2,0) AS int)

select CAST(ROUND(10.22255,2,0) AS NUMERIC(10,2))

DATE - Functions

SELECT GETDATE()

Date Parts: can use the name or listed abbreviations:

• year, yy, yyyy
• quarter, qq, q
• month, mm, m
• dayofyear, dy, y*
• day, dd, d*
• weekday, dw, w*
• week, wk, ww
• hour, hh
• minute, mi, n
• second, ss, s
• millisecond, ms
• microsecond, mcs
• nanosecond, ns

SELECT GETDATE(), MONTH(GETDATE()) , MONTH('20210101'), MONTH('2021-05-30 15:46:19.277')

SELECT GETDATE(), YEAR(GETDATE()) , YEAR('20210101'), YEAR('2021-05-30 15:46:19.277');

• 
  

SELECT EOMONTH(GETDATE()) as 'End Of Current Month',

   EOMONTH(GETDATE(),-1) as 'End Of Last Month',

   EOMONTH(GETDATE(),6) as 'End Of Month after +6 months';

    SELECT DATEADD(DAY,1,'2021-01-01') as 'Add 1 Day',

   DATEADD(WEEK,1,'2021-01-01') as 'Add 1 Week',

   DATEADD(MONTH,1,'2021-01-01') as 'Add 1 Month',

   DATEADD(YEAR,1,'2021-01-01') as 'Add 1 Year';

SELECT DATEDIFF(year, '2017/08/25', '2011/08/25')

SELECT CONVERT(CHAR(19), GETDATE(), 100) as 'Mon dd YYYY hh:mmAM';

CAST

SELECT 1 + CAST('11' AS INT) result;

SELECT CAST(5.95 AS INT) result

SELECT CAST('2019-03-14' AS DATETIME) result;

SELECT COALESCE(NULL, NULL, NULL, 'Test', NULL, 'Example.com');

IF ELSE

IF (1=1)

PRINT 'IF STATEMENT: CONDITION IS TRUE'

ELSE

PRINT 'ELSE STATEMENT: CONDITION IS FALSE'

select CAST(ROUND(10.255,2,0) AS int)

select CAST(ROUND(10.22255,2,0) AS NUMERIC(10,2))

GROUP BY

SELECT

 department_id,* 

FROM

 employees1 

GROUP

 BY

 department_id;

Various types of SQL aggregate functions are:

• Count()
• Sum()
• Avg()
• Min()
• Max()

SELECT

    department_id,

    COUNT(employee_id) headcount

FROM

    employees1

GROUP BY

    department_id;

SELECT

  ROUND(AVG(salary), 0) avg_salary

FROM

    employees1

GROUP BY department_id

SELECT

    department_id, SUM(salary)

FROM

    employees1

GROUP BY department_id

HAVING SUM(salary) BETWEEN 20000 AND 30000

ORDER BY SUM(salary);

constraint

NOT NULL: This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column any more.

UNIQUE: This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated.

PRIMARY KEY: A primary key is a field which can uniquely identify each row in a table. And this constraint is used to specify a field in a table as primary key.

FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a another table. And this constraint is used to specify a field as Foreign key.

CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition.

DEFAULT: This constraint specifies a default value for the column when no value is specified by the user.

CHECK constraint

CREATE TABLE Student

(

ID int NOT NULL,

NAME varchar(10) NOT NULL,

AGE int NOT NULL CHECK (AGE >= 18)

);

insert into Student VALUES(1,'test',16)

DEFAULT constraint

CREATE TABLE Student2

(

ID int NOT NULL,

NAME varchar(10) NOT NULL,

DATEADDED  DATE DEFAULT GETDATE()

);

insert into Student1(ID,NAME) VALUES(1,'test')