Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Sql > Sql Basis > Flashcards

Sql Basis Flashcards

1
Q

Sql NOT

A

Select * from employee where NOT gender=‘female’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Sql BETWEEN

A

Select * from employee where age between 20 and 25

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Sql COUNT

A

Select count(*) from employee

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

5 sql string function

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Sql AVG

A

Select avg(age) from employee

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Average age, group by dept

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
8
Q

Sql TRUNCATE

A

Truncate table employee;

    • delete all records
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Sql INNER JOIN

A

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?
1
Not at all
2
3
4
5
Perfectly
10
Q

Sql FULL JOIN

A

All records from both tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Sql LEFT JOIN

A

All records from first table and matching records from table 2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Delete command with join

Delete employee where dept. Loc=NY

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Sql UNION

A

Records from 2 tables without duplicate

Select… union select…

UNION ALL : with duplicate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Sql Operator merging 2 select

A

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?
1
Not at all
2
3
4
5
Perfectly
15
Q

Create VIEW

A

Create VIEW myView as

Select x from y where z

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Drop a view

A

Drop view myview

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Alter, add column

A

Alter table employee

Add dob date;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Alter table, drop column

A

Alter table employee

Drop column dob;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Sql merge

A 
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;
20
Q

Scalar valued function

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

To call it:
Select dbo.addfive(10)

21
Q

Table valued function (gender)

A

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’)

22
Q

Temporary table definition

A

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

23
Q

Create temporary table

A

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

Select * from #student

24
Q

Case statement

A 
Case
When condition1 then result1
When condition2 then result2
Else result
End
25
Q

Sql case example

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

Sql iif

A

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

27
Q

Store procedure definition

A

Sql code than can be saved and reused

Create procedure procedureName as
Sql_statement
Go;

To execute:
Exec procedureName

28
Q

Sore procedure example: age

A

Create procedure employeeAge
As select age from employee
Go

——-

Exec employeeAge;

29
Q

Store procedure with parameter definition

A 
Create procedure procName
@param1 type1, @param2 type2
As
Sql statement
Go
30
Q

Store procedure with parameter sample

A 
Create procedure employeeGender
@gender as varchar(20)
As
Select * from employee where @gender= e_gender
Go;

——

Exec employeeGender @gender=‘male’

31
Q

Create and use database

A

Create database dbname;

Use dbname;

32
Q

Error handling definition

Begin try
‘Sql statement
End try
Begin catch
‘Display error or rollback transaction
End catch
A

Try/catch block

33
Q

Try catch sample with divide by 0

A

Declare @val1
Declare @val2

Begin try
Set @val1=5
Set @val2=@val1/0
End try

Begin catch
Print error_message()
End catch

34
Q

Transaction with try catch

A

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

35
Q

Transaction roolback sequential

A

If age =45,

Begin transaction
Update employee set age =30 where id=10,

‘Run and then rollback, age will be 45
Rollback transaction

36
Q

System databases

A

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

37
Q

Activity monitor

A

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

38
Q

Indexes

A

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

39
Q

Create table

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

RDBMS

A

Relational database management system

41
Q

Select the number of different country in employee table

A

Select count(distinct country) from employee

42
Q

Select when name starts with a and finish with o

A

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

43
Q

Cast convert

A

Cast(address as varchar(30))
Convert(varchar(30),address)

Convert(varchar(30), modifiedDate,101)
101,103,1,3

44
Q

Concatenate 2 fields when second can be null

A

Select address1 + isnull(address2,’’)

Select isnull(color,’NA’)

45
Q

Standard security connectionstring

A

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

46
Q

Trusted connection string

A

Server=myserveraddress;
Database=mydatabase;
Trusted_Connection=true;

Server can be dot

Sql (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions