SQL LAB # COMPLETE TASKS

--1. get full details of tutors
--SELECT * FROM tutors

--2. Get the names and last names of all students residing in lodz
--SELECT name, last_name, city
--FROM students
--WHERE city  = 'Lodz'

--3.get the last name of all students with zip_code number = 12345
--SELECT  last_name, zip_code
--FROM students
--WHERE zip_code = 12345

--4.Get subjects names with an id number less than 2 and greater than 5. Use
--SELECT subject_name , subject_id
--FROM subjects
--WHERE  subject_id <2 OR subject_id  >5

 -- 5.Get the students numbers for all students that were born in Elsene or Los Angeles  (or both).
--SELECT name , birth_place
--FROM students
--WHERE  birth_place IN ( 'Elsene' ,'Los Angeles')

-- 6.Get the students numbers for all students that were born in Lodz and residing in
--Lodz or were born earlier than 1950. Use comparison operators.
 --SELECT student_id, name , birth_place, birth_date ,city
--FROM students
--WHERE ( birth_place IN ( 'lodz') AND city IN('lodz')) OR DATEPART (YEAR, birth_date ) < 1950

 --7.Get names of the birth_places for all students.
 --SELECT student_id, name , birth_place
--FROM students

--8. Get students numbers and names of all students who do not live in Lodz.
 --SELECT student_id, name ,city
--FROM students
--WHERE city NOT IN ( 'lodz')

--9.Get all the columns for every student whose zip_code number equals 12345, 22345 or 32445
--SELECT student_id, name ,zip_code
--FROM students
--WHERE zip_code IN ( 12345, 223445, 32445)

--10.Get all the columns for every student whose zip_code number not equals 12345, 22345 or 32445
--SELECT student_id, name ,zip_code
--FROM students
--WHERE zip_code NOT IN (12345, 223445, 32445)

-- 11.Get students numbers for all students that were born earlier than 1950 and later than 1940. Use the BETWEEN operator.
 --SELECT student_id, name ,birth_date
--FROM students
--WHERE DATEPART (YEAR, birth_date ) BETWEEN 1940 AND 1950

-- 12.Get students numbers for all students that were born earlier than 1950 and later than 1940. Use the BETWEEN operator.
--SELECT student_id, name ,birth_date
--FROM students
--WHERE DATEPART (YEAR, birth_date ) NOT BETWEEN 1940 AND 1950

--13. Get students numbers for all students residing in Lodz and which fax column is null.
--SELECT student_id, name , city , fax
--FROM students
--WHERE ( city  IN ( 'lodz')) OR fax IS  NULL

--14. Get students numbers for all students whose registration_date column is not null
--SELECT student_id, name ,registration_date
--FROM students
--WHERE registration_date IS NOT NULL

--15.Get the first and last names and numbers of all students whose first name contains the letter 'a' as the second character.
 --SELECT  student_id, name, last_name
--FROM students
--WHERE name LIKE '_a%'

--16.Get full details of all students whose name begins with a character in the range C through W
 --SELECT * FROM students
--WHERE name LIKE '[c-w]%'

-- 17.Get full details of all students whose last name does not begin with the letter J, K,L, M, N, or O
--and whose first name does not begin with the letter E or Z
 --SELECT * FROM students
--WHERE last_name NOT  LIKE '[JKLMNO]%' AND name  NOT  LIKE '[EZ]%'

--18.Get full details of all students whose name does not end with the 'n' character.
 --SELECT * FROM students
--WHERE name NOT  LIKE '%n'