There are move over 40 SQL queries and answers in this article. Queries are working on the following database.
SQL SELECT Queries Examples
Example 1: List all the records in the student chart
|
1 2 3 |
select * from students |
Example 2: List the nam,e surname and class of the student in the student table
|
1 2 3 |
select name, surname, class from students |
Example 3: List the gender Female (F) records in the student table
|
1 2 3 |
select * from students where gender='F' |
Example 4 : List the names of each class in the way of being seen once in the student table
|
1 2 3 |
select distinct class from students |
Example 5: List the students with Female gender and the class 10Math in the student table
|
1 2 3 |
select * from students where gender='F' and class='10Math' |
Example 6: List the names, surnames and classes of the students in the class 10Math or 10Sci in the student table
|
1 2 3 4 |
select name, surname, class from students where class='10Math' or class='10Sci' |
Example 7: List the students name surname and school number in the student table
|
1 2 3 |
select name, surname ,studentid as 'number' from students |
Example 8: List the students name and surname by combining them as name surname in the student table
|
1 2 3 |
select name+surname as 'Name Surname' from students |
Example 9: List the students with the names starting with “A” letter in the student table
|
1 2 3 |
select * from students where name like 'A%' |
Example 10: List the book names and pages count with number of pages between 50 and 200 in the book table
|
1 2 3 |
select name from books where pagecount between 50 and 200 |
Example 11: List the students with names Emma Sophia and Robert in the student table
|
1 2 3 |
select * from students where name in ('Emma','Sophia','Robert') |
Example 12: List the students with names starting with A D and K in the student table
|
1 2 3 |
select * from students where name like '[ADK]%' |
Example 13: List the names surnames classes and genders of males in 9Math or females in 9His in the student table
|
1 2 3 4 |
select name,surname,class,gender from students where (class='9Math' and gender='M') or (class='9His' and gender='F') |
Example 14: List the males whose classes are 10Math or 10Bio
|
1 2 3 4 |
select name, surname, class, gender from students where (sinif='10Bio' or sinif='10Math') and cinsiyet='M' |
Example 15: List the students with birth year 1989 in the student table
|
1 2 3 4 |
select * from students where birthdate between '01/01/1989' and '12/31/1989' |
Example 16: List the female students with student numbers between 30 and 50
|
1 2 3 4 |
select * from students where studentid between 30 and 70 and gender = 'F' |
Example 17: List the students according to their names
|
1 2 3 |
select * from students order by name |
Example 18: List the students by names for those with same names. List them by their surnames
|
1 2 3 |
select * from students order by name,surname |
Example 19: List the students in 10Math by decreasing school numbers
|
1 2 3 |
select * from students where class='10Math' order by ogrno desc |
Example 20: List the first 10 records in the student chart
|
1 2 3 |
select top 10 * from students |
Example 21: List the first 10 records name surname and date of birth information in the student table
|
1 2 3 |
select top 10 name,surname,birthdate from students |
Example 22: List the book with the most page number
|
1 2 3 |
select top 1 * from books order by pagecount desc |
Example 23: List the youngest student in the student table
|
1 2 3 |
select top 1 name,surname,birthdate from students order by birthdate desc |
Example 24: List the oldest student in the 10Math class
|
1 2 3 4 |
select top 1 name,surname,birthdate from students where class='10Math' order by birthdate |
Example 25: List the books with the second letter N
|
1 2 3 4 |
select * from books where name like '_n%' |
Example 26: List the students by grouping according to their classes
|
1 2 3 |
select * from students order by class |
Example 27: List the students to be different in each questioning randomly
|
1 2 3 |
select * from students order by newid() |
Example 28: Pick a random student from student table
|
1 2 3 |
select top 1 * from students order by newid() |
Example 29: Bring some random student’s name , surname and number from class 10Math
|
1 2 3 4 5 |
select top 1 ogrno,name,surname,class from students where class= '10Math' order by newid() |
SQL INSERT INTO Queries
Example 30: Add the writer named Smith Allen to the authors table
|
1 2 3 |
insert into authors(name,surname) values('Smith','Allen') |
Example 31: Add the genre of biography to the genre table
|
1 2 3 |
insert into types values('Biography ') |
Example 32: Add 10Math Class male named Thomas Nelson , 9Bio class female named Sally Allen and 11His Class female named Linda Sandra in one question
|
1 2 3 4 |
insert into students(name,surname,class,gender) values('Thomas','Nelson','10Math','M'),('Sally','Allen','9Bio','F'),('Linda','Sandra','11His ','F') |
Example 33: Add a random student in the students chart to the writers chart as an authors
|
1 2 3 4 5 |
insert into authors(name, surname) select top 1 name,surname from students order by newid() |
Example 34: Add students with student numbers between 10 and 30 as authors
|
1 2 3 4 |
insert into authors(name, surname) select name,surname from students where ogrno between 10 and 30 |
Example 35: Add the writer named Cindy Brown and make him write his writer number (Note: The last increased rate in automatic enhancing is hold in @@IDENTITY factor)
|
1 2 3 4 5 |
insert into authors(name, surname) values('Cindy','Brown') select @@IDENTITY |
SQL UPDATE Queries
Example 36: Change the class of the student whose school number is 3 from 10Bio to 10His
|
1 2 3 4 5 6 |
update students set class='10His' where studentid=3 and class='10Bio' --written to display query select * from students where studentid=3 |
Example 37: Transfer all the students in 9Math Class to 10Math Class
|
1 2 3 4 5 6 |
update students set class='10Math' where class='9Math' --written to display query select * from students where class='10Math' |
Example 38: Increase all of the students’ score by 5 points
|
1 2 3 4 5 6 |
update students set puan=score+5 --written to display query select * from students |
SQL DELETE Queries
NOTE 1: The most important thing in delete interrrogation is the part of condition.If the condition isn’t written, all records are included to the cleaning process..
NOT 2: TRUNCATE TABLE instruction will be given at the end of interrogation. (It is used for empting the table TRUNCATE TABLE TABLE NAME)
Example 39: Delete the author #25
|
1 2 3 4 |
delete from authors where authorId=25 --Related tables will be updated otomatically because database is regulated on fk settings as delete, update |
SELECT …….. IS NULL Command
When searching for the records whose rate are null in the select questıons, you can’t search for field area=null to research the area whose fieald area are null , İt is used null statement.
Example 40: List the students whose birth dates are null
|
1 2 3 4 |
select ograd,ogrsoyad,atarih from ogrenci,islem where ogrenci.ogrno=islem.ogrno |
PULLING DATA FROM MULTI TABLE WORKING QUESTIONS (without using JOIN)
Example 41: List the name ,surname and the dates of received books of the student
|
1 2 3 4 5 |
select students.name, students.surname, students.birthdate from student,borrows where students.studentId=borrows.borrowId |
Good tutorials for basic SQL queries, thanks.
[…] For more SQL SELECT Examples click here! […]
[…] You may also like: SQL Query Examples(40+) […]