Truncate table employee; - - delete all records

Sql Basis Flashcards by Olivier PICQ

Sql NOT

Select * from employee where NOT gender=‘female’

How well did you know this?

Not at all

Perfectly

Sql BETWEEN

Select * from employee where age between 20 and 25

How well did you know this?

Not at all

Perfectly

Sql COUNT

Select count(*) from employee

How well did you know this?

Not at all

Perfectly

5 sql string function

ltrim(), lower(),upper(), reverse(), substring(string,start,lenght)

How well did you know this?

Not at all

Perfectly

Sql AVG

Select avg(age) from employee

How well did you know this?

Not at all

Perfectly

Average age, group by dept

Select avg(age),dpmt from employee group by dpmt order by avg(age) desc

How well did you know this?

Not at all

Perfectly

Group by dpmt, avg(salary) when avg salary>100k

Select avg(salary),dptm from employee group by dpmt having avg(salary)>100000 order by avg(salary) desc.

GROUP BY - HAVING - ORDER

Having must be used in conjonction of group by

How well did you know this?

Not at all

Perfectly

Sql TRUNCATE

Truncate table employee;

- delete all records

How well did you know this?

Not at all

Perfectly

Sql INNER JOIN

Records only matching in the 2 tables

Select employee.name, employee.dpmt,department.name,department.location from employee inner join department on employee.dpmt=department.name

How well did you know this?

Not at all

Perfectly

Sql FULL JOIN

All records from both tables

How well did you know this?

Not at all

Perfectly

Sql LEFT JOIN

All records from first table and matching records from table 2

How well did you know this?

Not at all

Perfectly

Delete command with join

Delete employee where dept. Loc=NY

Delete employee from employee join department on employee.dept=department.name where department.location=‘NY’

How well did you know this?

Not at all

Perfectly

Sql UNION

Records from 2 tables without duplicate

Select… union select…

UNION ALL : with duplicate

How well did you know this?

Not at all

Perfectly

Sql Operator merging 2 select

Union : no duplicate
Union all: with duplicate
Except: remove record from second table
Intersect: common columns from the 2 tables

How well did you know this?

Not at all

Perfectly

Create VIEW

Create VIEW myView as

Select x from y where z

How well did you know this?

Not at all

Perfectly

Drop a view

Drop view myview

How well did you know this?

Not at all

Perfectly

Alter, add column

Alter table employee

Add dob date;

How well did you know this?

Not at all

Perfectly

Alter table, drop column

Alter table employee

Drop column dob;

How well did you know this?

Not at all

Perfectly

Sql merge

Study These Flashcards

Merge employee_target T
Using employee_source S
On T.id=S.id
When matched
Then update set T.age=S.age, t.salary=...
When not matched by target
Then insert (age,salary...) values 
(S.age, S.salary...)
When not matched by source
Then delete;

Scalar valued function

Study These Flashcards

Create function addfive(@num as int)
Returns int
As
Begin
Return(@num+5)
End

To call it:
Select dbo.addfive(10)

Table valued function (gender)

Study These Flashcards

Create function selectGender(@gender as varchar(20)) returns table
As
Return
( select * from employee where e_gender=@gender)

—to use it:
Select * from dbo.selectGender(‘male’)

Temporary table definition

Study These Flashcards

Stored in tempdb. Deleted as soon ad the session is terminated, syntax is the same than create normal table but with a # before the name

Create temporary table

Study These Flashcards

Create table #student
( s_id int,
s_name varchar(20)
);

Select * from #student

Case statement

Study These Flashcards

Case
When condition1 then result1
When condition2 then result2
Else result
End

Sql case example

``` Select *,grade= Case When salary<90000 then ‘C’ When salary<120000 then ‘B’ Else ‘A’ End from employee Go ```

Sql iif

Select *, iif(age<30,’young’,’old’) as generation From employee

Store procedure definition

Sql code than can be saved and reused Create procedure procedureName as Sql_statement Go; To execute: Exec procedureName

Sore procedure example: age

Create procedure employeeAge As select age from employee Go ——- Exec employeeAge;

Store procedure with parameter definition

``` Create procedure procName @param1 type1, @param2 type2 As Sql statement Go ```

Store procedure with parameter sample

``` Create procedure employeeGender @gender as varchar(20) As Select * from employee where @gender= e_gender Go; ``` —— Exec employeeGender @gender=‘male’

Create and use database

Create database dbname; Use dbname;

Error handling definition ``` Begin try ‘Sql statement End try Begin catch ‘Display error or rollback transaction End catch ```

Try/catch block

Try catch sample with divide by 0

Declare @val1 Declare @val2 Begin try Set @val1=5 Set @val2=@val1/0 End try Begin catch Print error_message() End catch

Transaction with try catch

Begin try Begin transaction Update employee set salary=100 where gender=‘male’ Update employee set salary=100/0 where gender=‘female’ Commit transaction Print ‘transaction commited’ End try Begin catch Rollback transaction Print ‘transaction rolledback’ End catch

Transaction roolback sequential

If age =45, Begin transaction Update employee set age =30 where id=10, ‘Run and then rollback, age will be 45 Rollback transaction

System databases

Master:settings, metadata, location of db... Model:template for new user db Msdb: sql agent for scheduling alerts and jobs Tempdb: temp database deleted after every restart

Activity monitor

In ssms, right click on the server and choose activity monitor

Indexes

Can be clustered or not clustered. Can be created from indexes tab. After table is modified, table is fragmented, index less efficient, need to be rebuild: right click on the index and rebuild or reorganized

Create table

``` Create table employee ( E_id int not null, E_name varchar(20), E_age int, Primary key (E_id) ); ```

RDBMS

Relational database management system

Select the number of different country in employee table

Select count(distinct country) from employee

Select when name starts with a and finish with o

Select * from employee where name like ‘a%o’

Cast convert

Cast(address as varchar(30)) Convert(varchar(30),address) Convert(varchar(30), modifiedDate,101) 101,103,1,3

Concatenate 2 fields when second can be null

Select address1 + isnull(address2,’’) Select isnull(color,’NA’)

Standard security connectionstring

Server=myServerAddress; Database=mydatabase; User id=myuserName Password=myPassword

Trusted connection string

Server=myserveraddress; Database=mydatabase; Trusted_Connection=true; Server can be dot

Sql Basis Flashcards

(46 cards)