sql

Question 1

upercase = lowercase # are the same

Answer 1

SELECT first_name FROM patients where first_name like ‘C%’
SELECT first_name FROM patients where first_name like ‘c%’

Question 1

like

Answer 1

SELECT first_name FROM patients where first_name like ‘C%’

Question 2

between:
is >= <=
not ><

Answer 2

SELECT first_name, last_name,weight
FROM patients
WHERE weight BETWEEN 100 AND 120;

Question 3

operation card

Answer 3

– Uso del operador AND
SELECT *
FROM your_table
WHERE condition1 AND condition2;

– Uso del operador OR
SELECT *
FROM your_table
WHERE condition1 OR condition2;

– Uso del operador NOT
SELECT *
FROM your_table
WHERE NOT condition;

Question 4

con NULL nunca va =

Answer 4

update patients set allergies=’NKA’ where allergies is null

Question 5

concatenar || && concat(, , ,)

Answer 5

SELECT first_name || ‘ ‘ || last_name FROM patients
SELECT

Question 6

join on

Answer 6

SELECT table1.col1, table2.col3 where table
join on table2 table1.col2 = table2.col4

Question 7

view all the tables => sql server

Answer 7

select * from sys.tables
select name from sys.tables

Question 8

delete all elements of a table

Answer 8

delete Personas

Question 9

drop table

Answer 9

drop table Personas

Question 10

create a table with date

Answer 10

CREATE TABLE Personas ( last_name NVARCHAR(50),
birthday DATE
);

also works => string
CREATE TABLE Personas ( last_name NVARCHAR(50),
birthday NVARCHAR(50),
);

Question 11

do questions

Answer 11

SELECT * FROM patients where Year(Birthday)=2010

Question 12

count

Answer 12

SELECT count(*) FROM patients

Question 13

max

Answer 13

example more complet

SELECT max(height) FROM patients

SELECT first_name,last_name,height FROM patients
where height= (SELECT max(height) FROM patients)

SELECT first_name,last_name,MAX(height) as height FROM patients

Question 14

in

Answer 14

SELECT * FROM patients
where patient_id in (1,45,534,879,1000)

Question 15

top

Answer 15

select top(10) * from Personas // in SQL SERVER
select * from Personas LIMIT 10 // in SQL normal

Question 16

compare columns

Answer 16

SELECT * FROM admissions where admission_date = discharge_date

Question 17

distinct

Answer 17

SELECT distinct(city) FROM patients /* all patients*/

SELECT distinct(city) FROM patients where province_id=’NS’

Question 18

order by

Answer 18

SELECT * FROM patients order by height

SELECT * FROM patients order by height DESC

Question 19

group by

los valores de ONE no se van a repetir => group by ONE
los valores de TWO no se van a repetir => group by ONE, TWO

Answer 19

SELECT year(birth_date) FROM patients group by year(birth_date)

alse work (important) in sql server dont work
SELECT * FROM admissions group by admission_date

Question 20

having
only use when you use => GROUP BY
it is like a WHERE

Answer 20

example 1

select first_name from patients group by first_name
having count(first_name)=1

select first_name from patients group by first_name having last_name = ‘a%’

Question 21

least 6 characters long

Answer 21

SELECT
patient_id,
first_name
FROM patients
WHERE first_name LIKE ‘s____%s’;

Question 22

len

Answer 22

SELECT
patient_id,
first_name
FROM patients
WHERE first_name LIKE ‘s%s’ and len(first_name)>=6;

Question 23

Order with 2 values

Order the list by the length of each name and then by alphabetically.

Answer 23

SELECT
first_name
FROM patients order by len(first_name), order by first_name

Question 24

create a table of horizontal

Answer 24

simple

select
(SELECT count(gender) FROM patients where gender=’M’),
(SELECT count(gender) FROM patients where gender=’F’)

select
(SELECT count(gender) FROM patients where gender=’M’) as one,
(SELECT count(gender) FROM patients where gender=’F’) as two

select gender,count(*) from patients group by gender

Question 25

union
a=[1,2,3] b=[2,3,4] a UNION b =[1,2,3,4]

union all
a=[1,2,3] b=[2,3,4] a UNION b =[1,2,3,4]

Answer 25

SELECT first_name, last_name, ‘Doctor’ FROM doctors
union ALL /UNION/
SELECT first_name, last_name, ‘Patient’ FROM patients

Question 26

UPPER & LOWER

Answer 26

SELECT upper(last_name) from patients
SELECT lower(last_name) from patients

Question 27

HAVING is like WHERE => in group by

Question 28

sum

Answer 28

SELECT province_id,sum(height) as two FROM patients group by province_id

Question 29

IMPORTANT => works also in SQL SERVE

Answer 29

example 3

select emp_no,COUNT(*) from works_on group by emp_no

Question 30

subtract // RESTAR

Answer 30

example 1

SELECT MAX(weight) - MIN(weight) FROM patients

SELECT 200 - 100 FROM patients
=> 100 // equal columns of table

Question 32

where & having

where => go first
having => second

Question 33

ALLOW

HAVING admission_date = MAX(admission_date); //well

WHERE admission_date = MAX(admission_date); //wrong

Question 34

you can use MAX whit date

Answer 34

select max(admission_date) from admissions // 2019

select min(admission_date) from admissions // 2018

Question 35

work => % // equal JS

Answer 35

where (patient_id % 2 =1 // odd number :)

Question 36

use at the same time => JOIN & GROUP BY

Answer 36

SELECT first_name, last_name, COUNT(*) as admissions_count
FROM admissions
JOIN doctors ON doctors.doctor_id = admissions.attending_doctor_id
GROUP BY first_name, last_name
ORDER BY admissions_count DESC;

Question 37

producto cartesiano

Answer 37

select * from TABLE1,TABLE2

Question 38

you can join => min, max, name_expample * from aaa group by

For each doctor, display their id, full name, and the first and last admission date they attended.

Answer 38

select doctor_id , first_name,max(admission_date),min(admission_date) from doctors
join admissions on doctors.doctor_id = admissions.atending.doctor_id group by first_name, doctro_id

Question 39

case

Answer 39

SELECT
nombre,
CASE
WHEN edad < 18 THEN ‘Menor de edad’
WHEN edad >= 18 AND edad < 65 THEN ‘Adulto’
ELSE ‘Jubilado’
END AS grupo_edad
FROM
personas;

Question 40

left join // right join

table:ONE
id nombre precio
1 Camisa 5.00
2 Pantalón
3 Zapatos

table:TWO
id Annoyed
1 talkative
2 reliable

Answer 40

ONE left join TWO

ONE.id nombre precio Annoyed
1 Camisa 25.00 talkative
2 Pantalón 40.00 reliable
3 Zapatos 50.00 NULL

TWO.id nombre precio Annoyed
1 Camisa 25.00 talkative
2 Pantalón 40.00 reliable

Question 41

floor

Answer 41

floor(1.5) = 1
floor(1.1) = 1
floor(1.99) = 1
floor(2) = 2

Question 42

POWER # pow :)

Answer 42

POWER(height/100.0,2)

Question 43

LIMIT

Answer 43

select one,two,three from patients limit 100

Question 44

divide // how to divide (divaid)

100/7

Answer 44

wrong

select 100/7 from patients limit 1
=> 14

well
select (100100.0)/(7100) from patients limit 1
=> 14.285

Answer 45

wrong

well
select sum(gender=’M’) from patients
select sum(gender=’F’) from patients

select count(gender=’M’) from patients
select count(gender=’F’) from patients