Brainscape's Knowledge GenomeTM

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


See full index

SQL > Query > Flashcards

Query Flashcards

1
Q

query to create ,use and delete a database?

A

create database happy;
use happy;
drop database happy;

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

create and add values to a table?

A

create table employee(
e_id int not null,
e_name varchar(20),
primary key(e_id)
)

insert into employee values(
1,’name’)

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

select one column,multiple column,every column,distinct values in a column?

A

select x from employee;
select x,y from employee;
select * from employee;
select distinct x from employee;

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

where clause?

A

select * from employee where x=’female’;

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

AND ,OR, NOT operators?

A

select * from employee where cond1 AND cond2;
select * from employee where cond1 OR cond2;
select * from employee where NOT cond1;

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

LIKE and BETWEEN operators?

A

select * from employee where x LIKE ‘j%’;
select * from employee where x LIKE ‘3_’;

select * from employee where x BETWEEN 12 and 28;

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

query for different functions in sql?

A

min()
max()
count()
sum()
avg()

select min(x) from employee;
select * from employee where x=’male’;

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

query for different string functions?

A

LTRIM()
LOWER()
UPPER()
REVERSE()
SUBSTRING()

select LTRIM(‘ hello’);
select UPPER(‘hello’);
select SUBSTRING(‘this is sparta’,9,6);

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

ORDER BY and TOP?

A

select * from employee order by e_salary DESC;
select TOP 3 * from employee order by e_age DESC;

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

GROUP BY and HAVING clause?

A

select avg(e_salary),e_gender from employee GROUP BY e_gender;
select avg(e_age),e_dept from employee GROUP BY e_dept ORDER BY avg(e_age) DESC;

select e_dept,avg(e_salary) as avg_salary from employee GROUP BY
e_dept HAVING avg(e_salary)>100000 order by avg(e_salary) DESC;

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

update,delete and truncate statement?

A

update employee set e_age=42 where e_name=’sam’ ;
delete from employee where e_age=42;
truncate table employee;

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

different joints in SQL?

A

select employee.e_name,employee.e_dept ,department.d_name,department.d_location
FROM employee INNER JOIN department
ON employee.dept=department.d_name;

select employee.e_name,employee.e_dept ,department.d_name,department.d_location
FROM employee LEFT JOIN department
ON employee.dept=department.d_name;

select employee.e_name,employee.e_dept ,department.d_name,department.d_location
FROM employee RIGHT JOIN department
ON employee.dept=department.d_name;

select employee.e_name,employee.e_dept ,department.d_name,department.d_location
FROM employee FULL JOIN department
ON employee.dept=department.d_name;

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

update using JOIN?

A

update employee;
set e_age=e_age+10
FROM employee JOIN department
ON employee.dept=department.d_name
WHERE d_location=’newyork’

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

delete using JOIN?

A

delete employee
FROM employee JOIN department
ON employee.dept=department.d_name
where d_location=’newyork’

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

Union,except,intesect operators?

A

union

select * from x
union
select * from y

union all

select * from x
union all
select * from y

except

select * from x
except
select * from y

intersect

select * from x
intersect
select * from y

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

view in sql?

A

it is a subset table upon we can work

create view female_employee as
select * from employee
where e_gender=’female’;

drop view female_employee;

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

Alter in sql?

A

add,delete,modify columns in a table

alter table employee;
add e_dob date;

alter table employee;
drop column e_dob;

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

Merge in sql?

A

merge joins two tables source and target based on a common column

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

used defined function in sql?

A

scalar function-returns an scalar value
table valued function-returns a table

scalar function

CREATE FUNCTION function_name(@parm1 data_type,@parm2 data_type)
RETURNS return_datatype
AS
BEGIN
(function body)
RETURN value
END

table valued function

CREATE FUNCTION function_name(@parm1 datatype,@parm2 datatype)
RETURNS table
AS
RETURN (select columnlist from table where cond)

select * from dbo.function_name()

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

what is temporary table?

A

used to create temporary table and they are deleted as soon as the session is terminated,used to store and access intermediate data

CREATE TABLE #student(
s_id int,
s_name varchar(20)
);

insert into #student values(
)

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

what is case statement?

A

it helps in multi way decision making

select
case
when cond1 then x
when cond2 then y
when cond3 then z
else cond4
end;

22
Q

what is IIF function?

A

similar to case statement-use boolean to determine result
IMMEDIATE IF

select * ,age=
IIF(employee.age>30,’old’,’young’)
from employee
go

23
Q

what is stored procedure in sql?

A

it is a prepared sql code which can be saved and reused

CREATE PROCEDURE procedure name
AS
sql_statement
GO;

EXEC procedure name

we can also use parameters in our function

CREATE PROCEDURE procedure name @param1 datatype

24
Q

what is exception handling?

A

the error condition that occurs during a process is called as exception and the way which we handle that error is called exception handling

declare @val1 int;
declare @val2 int;

BEGIN TRY
set @val1=8;
set @val2=9/0;
END TRY
BEGIN CATCH
print error_message()
END CATCH

25
Q

what is transaction in sql?

A

transaction is a group of commands that change data stored in a database
transaction makes sure that all of the commands are executed or none of them are
if any one of the commands fail then all of them fails and any data that has been modified will rollback

BEGIN TRY
BEGIN TRANSACTION
update employee set e_salary=x where cond
update employee set e_age=y where cond
commit transaction
print ‘transaction committed’
END TRY
BEGIN CATCH
rollback transaction
print ‘transaction rollback’
END CATCH

COMMIT permanently saves the changes made by the current transaction.
ROLLBACK undo the changes made by the current transaction

26
Q

Query to select second max from something?

A

select max(age) from details
where age not in (select max(age) from details);

27
Q

Query to select nth max data from something?

A

select age from details s1 where
(n-1)=(select count(distinct(age)) from details s2 where s2.age>s1.age)

28
Q

Query to grant and revoke permission?

A

Grant

GRANT privilege_name ON object_name TO user_name

Revoke

REVOKE privilege_name ON object_name FROM user_name

eg:
GRANT ALL ON employee TO abc;

29
Q

USING vs ON clause?

A

USING clause-allows you to specify the join key by name.you have to have the same column name in both the table

SELECT department_name,city
FROM departments
JOIN locations
USING (location_id)

ON clause-allows you to join columns with different names

SELECT department_name,city
FROM department
JOIN locations
ON (dept.location_id=loc.id)

30
Q

RESULT OFFSET clause?

A

declares the number of rows to skip at the start of the query result

select * from employee
offset 5 rows

31
Q

IN operator?

A

allows you to specify multiple values

SELECT * FROM EMPLOYEE
WHERE e_age IN (20,21,22,23);

32
Q

How to fetch current date in SQL?

A

SELECT GETDATE();

33
Q

create a new table from an old table copying its
1-structure
2-stucture and content

A

structure

select * into newtable from oldtable where 1=0;

structure and content

select * into newtable from oldtable;

34
Q

select even and odd rows from a table?

A

select * from patientcheckup where mod(patient_id,2)=0;
select * from patientcheckup where mod(patient_id,2)=1;

35
Q

how to select duplicate rows from a table?

A

using primary key

select p_id,count() from table group by p_id having count()>1;

without using primary key

select x,y,z,count() from table group by x,y,z having count()>1;

36
Q

add values to a column after altering the table with a new column

A

update table set column=’y’ where patient_id=4;

37
Q

select patients from the same state?

A

select distinct p.id,p.name.p.state from table p,table p1
where p.state=p1.state and p.id!=p1.id;

38
Q

select first and last row from the table?

A

select * from patientcheckup where patient_id=(select max(patient_id) from patientcheckup);

select * from patientcheckup where patient_id=(select min(patient_id) from patientcheckup);

39
Q

query to fetch consultation fees wise count and sort them in descending order

A

select fees,count() as count from patientcheckup group by fees order by count() asc;

count(*) is used to find the count of particular row by condition

39
Q

query to fetch consultation fees wise count and sort them in descending order

A

select fees,count() as count from patientcheckup group by fees order by count() asc;

count(*) is used to find the count of particular row by condition

40
Q

select top x and bottom x columns from the table

A

select Top 2 * from patientcheckup order by patient_id asc;
select Top 2 * from patientcheckup order by patient_id desc;

41
Q

select all patients who joined in a year

A

SELECT * FROM Patients
WHERE RegDate BETWEEN ‘2021/01/01’ AND ‘2021/12/31’;

42
Q

select 50% data from the table

A

select * from patientcheckup where patient_id=(select count(patient_id)/2 from patientcheckup)

43
Q

select name of patient who paid consultation fees between x and y after joining

A

select patient_name from patienttable
join patientcheckup
on patienttable.patient_id=patientcheckup.patient_id
where fees between 200 and 500;

44
Q

update table to remove trailing and leading spaces from patient_name

A

update table
set patient_name=LRIM(RTRIM(patient_name))

45
Q

select details of every patient except ‘sheela’ and ‘anay’

A

select * from patienttable where patient_name not in (‘sheela’,’anay’)

46
Q

find the count of a letter from the patient name in a table

A

select patient_id,patient_name,LENGTH(patient_name)-LENGTH(REPLACE(patient_name,’a’,’’)) as count from patienttable

47
Q

query to select first three letters from the patient name

A

select patient_name,SUBSTRING(patient_name,1,3) from patienttable;

48
Q

query to concat two column into a new column

A

SELECT CONCAT(Address, ‘ ‘, State) AS ‘NewAddress’ FROM Patients;

49
Q

command used to return the position of a letter from a string

A

CHARINDEX(‘ ‘,address)

SELECT SUBSTRING(Address, 1, CHARINDEX(‘ ‘,Address)) FROM Patients;

select the string upto ‘ ‘

SQL (2 decks)

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