1. Create table STUDENTS having columns roll _no (NUMBER), name (VARCHAR(20), Age (NUMBER), Address (VARCHAR(30)), DateOfBirth (DATE).
2. Insert at least ten records of student.
3. Structure of table STUDENTS.
4. Job_title of JOBS table having minimum salary of Rs. 20000.
5. Records of student belonging to Moradabad.
6. Create table EMP from the table EMP present in account “hr/scott”.
7. Create table DEPT from the table DEPT present in account “scott”.
8. Name of all tables present in your account.
9. Create a table STUDENTINFO.
10. Insert all the above values in the table.
11. All the records from the table STUDENTINFO.
12. First name and last name of SID 98002.
13. First name and age for everyone that is in the table.
14. Records of student living in Arizona.
15. Records from table whose age is not 23,32,35,42.
16. Records from table whose age is 32,35,42.
17. Record from the table whose first or last name starts with ‘G’.
18. SID, first name, last name and city for everyone who have taken admission after 2-Apr-81.
19. All columns for everyone that is over 40 years old.
20. First name, last name & city from the table whose names starts with ‘Er’.
21. First name and last name from the table whose last names ends with ‘S’.
22. Details from the table whose first name equals ‘Eric’ exactly.
23. First and last names for everyone whose last name ends with an ‘ay’.
24. All columns for everyone whose first name equals ‘Mary’.
25. All columns for everyone whose first name contains ‘Mary’.
26. First name, last name & age from the table where last name contains ‘illia’.
27. First name, last name, city & state whose first name starts with ‘J’.
28. First name, last name and city for everyone that is not from ‘Payson’.
29. First name of students whose name ends with "n".
30. First name of students whose name starts with "S".
31. First name of students having "r" as a second character of their name.
32. Details of students whose SID is greater than 22000 & less than 88000.
33. Students who are eligible for admission.
34. Name of the students whose SID are 22322, 32326, 32380, 98002.
35. Student first and last name & SID whose SID is between 32000 & 92000.
36. Different city available in the STUDENTINFO table.
37. Names of student who belongs to Arizona & California.
38. Name of the students whose last names have exactly five characters.
39. No. of city available in the STUDENTINFO table.
40. Students whose admission date is null.
Some questions for beginners
1. Display the data of JOBS table.
select * from JOBS
2. Display Job_title from JOBS.
select Job_title from JOBS
3. Display the records of Rs. 4200 minimum salary.
select * from JOBS where min_salary = 4200
4. Display Job_title of JOBS table having minimum salary of Rs. 20000.
select Job_title from Jobs where min_salary = 20000
5. Display Job_ID of JOBS which is having > 16000 in maximum salary.
select Job_ID from Jobs where max_salary > 16000
6. Display Job_ID of the JOBS where maximum salary is 9000 and minimum salary is 4200.
select Job_ID from Jobs where min_salary = 4200 and max_salary = 9000
DBMS 1
| -- STUDENTINFO TABLE DESCRIPTION -- | |||
|---|---|---|---|
| Column Name | Data Type | Size | Constraints |
| SID | Number | 5 | Primary key |
| FIRST | Varchar2 | 20 | |
| LAST | Varchar2 | 20 | |
| AGE | Number | 4 | > 0 |
| CITY | Varchar2 | 15 | |
| STATE | Varchar2 | 15 | |
| ADMISSION | Date | ||
| -- STUDENTINFO TABLE -- | ||||||
|---|---|---|---|---|---|---|
| SID | FIRST | LAST | AGE | CITY | STATE | ADMISSION |
| 99980 | John | Jones | 45 | Payson | Arizona | 17-Dec-80 |
| 99982 | Mary | Jones | 25 | Payson | Arizona | 20-Feb-81 |
| 88232 | Eric | Edwards | 32 | San Diego | California | 22-Feb-81 |
| 88233 | Mary | AnnEdwards | 32 | Phonix | Arizona | 2-Apr-81 |
| 98002 | Ginger | Howell | 42 | Cottonwood | Arizona | 28-Sep-81 |
| 92001 | Sebastian | Smith | 23 | Gila Bend | Arizona | 1-May-81 |
| 22322 | Gus | May | 35 | Begdad | Arizona | 9-Jun-81 |
| 32326 | Mary | AnnGray | 52 | Tucson | Arizona | 9-Dec-82 |
| 32327 | Erica | William | 60 | ShowLow | Arizona | 17-Nov-81 |
| 32380 | Leoroy | Brown | 22 | Pinetop | Arizona | 8-Sep-81 |
1. Create table STUDENTS having columns roll _no (NUMBER), name (VARCHAR(20), Age (NUMBER), Address (VARCHAR(30)), DateOfBirth (DATE).
Create table STUDENTS(
ROLL_NO NUMBER,
NAME VARCHAR2(20),
AGE NUMBER,
ADDRESS VARCHAR2(30),
DATE_OF_BIRTH DATE)
2. Insert at least ten records of student.
insert into STUDENTS values(1,'ABC',18,'Rudrapur','1-1-08')
insert into STUDENTS values(2,'DEF',18,'Kashipur','1-1-08')
insert into STUDENTS values(3,'GHI',18,'Moradabad','1-1-08')
insert into STUDENTS values(4,'JKL',19,'Bilashpur','1-1-07')
insert into STUDENTS values(5,'MNO',18,'Rudrapur','1-1-08')
insert into STUDENTS values(6,'PQR',18,'Haldwani','1-1-08')
insert into STUDENTS values(7,'STU',19,'Moradabad','1-1-07')
insert into STUDENTS values(8,'VWX',18,'Kashipur','1-1-08')
insert into STUDENTS values(9,'YZ',19,'Gadarpur','1-1-07')
insert into STUDENTS values(10,'ABCD',18,'Nainital','1-1-08')
3. Display the structure of table STUDENTS.
DESC STUDENTS
4. Display the records present in STUDENTS table.
select * from STUDENTS
5. Display the records of student belonging to Moradabad.
select * from STUDENTS where address = 'Moradabad'
6. Create table EMP from the table EMP present in account “hr/scott”.
Create table EMP as select * from hr.EMP
7. Create table DEPT from the table DEPT present in account “scott”.
Create table DEPT as select * from scott.DEPT
8. Display the name of all tables present in your account.
select * from user_tables
9. Create a table STUDENTINFO.
Create table STUDENTINFO(
SID Number(5) Primary Key,
FIRST Varchar2(20),
LAST Varchar2(20),
AGE Number(4) check(AGE > 0),
CITY Varchar2(15),
STATE Varchar2(15),
ADMISSION Date)
10. Insert all the above values in the table.
insert into STUDENTINFO values(99980,'John','Jones', 45,'Payson','Arizona','17-Dec-80')
insert into STUDENTINFO values(99982,'Mary','Jones', 25,'Payson','Arizona','20-Feb-81')
insert into STUDENTINFO values(88233,'Mary Ann','Edwards', 32,'Phonix','Arizona','2-Apr-81')
insert into STUDENTINFO values(88232,'Eric','Edwards', 32,'San Diego','California','22-Feb-81')
insert into STUDENTINFO values(98002,'Ginger','Howell', 42,'Cottonwood','Arizona','28-Sep-81')
insert into STUDENTINFO values(92001,'Sebastian','Smith', 23,'Gila Bend','Arizona','1-May-81')
insert into STUDENTINFO values(22322,'Gus','May', 35,'Begdad','Arizona','9-Jun-81')
insert into STUDENTINFO values(32326,'Mary Ann','Gray', 52,'Tucson','Arizona','9-Dec-82')
11. Select all the records from the table STUDENTINFO.
select * from STUDENTINFO
12. Select first name and last name of SID 98002.
select first, last from STUDENTINFO where sid = 98002
13. Display the first name and age for everyone that is in the table.
select first, age from STUDENTINFO
14. Display the records of student living in Arizona.
select * from STUDENTINFO where state like 'Arizona'
15. Display the records from table whose age is not 23,32,35,42.
select * from STUDENTINFO where age not in (23, 32, 35, 42)
16. Display the records from table whose age is 32,35,42.
select * from STUDENTINFO where age in (32, 35, 42)
17. Display the record from the table whose first or last name starts with ‘G’.
select * from STUDENTINFO where first like 'G%' or last like 'G%'
18. Display the SID, first name, last name and city for everyone who have taken admission after 2-Apr-81.
select SID, first, last, city from STUDENTINFO where admission > '2-Apr-81'
19. Display all columns for everyone that is over 40 years old.
select * from STUDENTINFO where age > 40
20. Select all the first name, last name & city from the table whose names starts with ‘Er’.
select first, last, city from STUDENTINFO where first like 'Er%'
21. Select first name and last name from the table whose last names ends with ‘S’.
select first, last from STUDENTINFO where last like '%s'
22. Select all the details from the table whose first name equals ‘Eric’ exactly.
select * from STUDENTINFO where first like 'Eric'
23. Display the first and last names for everyone whose last name ends with an ‘ay’.
select first, last from STUDENTINFO where last like '%ay'
24. Display all columns for everyone whose first name equals ‘Mary’.
select * from STUDENTINFO where first like 'Mary'
25. Display all columns for everyone whose first name contains ‘Mary’.
select * from STUDENTINFO where first like 'Mary%'
26. Select the first name, last name & age from the table where last name contains ‘illia’.
select first, last, age from STUDENTINFO where last like '%illia%'
27. Select the first name, last name, city & state whose first name starts with ‘J’.
select first, last, city, state from STUDENTINFO where first like 'J%'
28. Display the first name, last name and city for everyone that is not from ‘Payson’.
select first, last, city from STUDENTINFO where city not like 'Payson'
29. List the first name of students whose name ends with "n".
select first from STUDENTINFO where last like '%n'
30. List the first name of students whose name starts with "S".
select first from STUDENTINFO where first like 'S%'
31. List the first name of students having "r" as a second character of their name.
select first from STUDENTINFO where first like '_r%'
32. List the details of students whose SID is greater than 22000 & less than 88000.
select * from STUDENTINFO where SID > 22000 and SID < 88000
33. List the students who are eligible for admission.
select * from STUDENTINFO where age <= 50
34. List the name of the students whose SID are 22322, 32326, 32380, 98002.
select * from STUDENTINFO where SID in (22322, 32326, 32380, 98002)
35. List the student first and last name & SID whose SID is between 32000 & 92000.
select first, last, SID from STUDENTINFO where SID >= 32000 and SID <= 92000
36. List the different city available in the STUDENTINFO table.
select distinct city from STUDENTINFO
37. List the names of student who belongs to Arizona & California.
select first, last from STUDENTINFO where state in ('California', 'Arizona')
38. List the name of the students whose last names have exactly five characters.
select first, last from STUDENTINFO where last like '_____'
39. List the no. of city available in the STUDENTINFO table.
select count (distinct city) from STUDENTINFO
40. List the students whose admission date is null.
select * from STUDENTINFO where admission is NULL