Extended Clause

Question 1

Write a query to display employee_ID, first_name, last_name, salary from employees table where salary is greater than 6000

Answer 1

Select employee_ID, first_name, last_name, salary from employees; where salary>6000

Question 2

Write a query to display employee_ID, first_name, last_name, department_ID, salary where salary is greater than 7000 and department_ID is equal to 90

Answer 2

Select employee_ID, first_name, last_name, department_ID, salary where salary > 7000 AND department_ID = 90;

Question 3

Write a query to display employee_ID, first_name, last_name, department_ID, salary where salary is greater than 7000 or department_ID = 90;

Answer 3

Select employee_ID, first_name, last_name, salary, department_ID
from employees
Where salary > 7000 OR department_ID = 90;

Question 4

Write a query to display all records from employees table where department_ID is between 30 to 90

Answer 4

Select * from employees
where Department_ID Between 30 and 90;

Question 5

Write a query to display employee_ID , first_name, last_name, salary, department_ID from employees where department_ID is 30 or 60 or 90

Answer 5

Select employee_ID, first_name, last_name, salary, department_ID
from employees
Where department_ID IN(30,60,90);

Question 6

Write a query to display employee_ID, first_name, last_name in uppercase, salary from employees table

Answer 6

Select employee_ID, first_name, UPPER(last_name) AS last_name, salary from employees;

Question 7

Write a query to display all columns and last_name in uppercase from employees table

Answer 7

Select *, upper(last_name) AS upper_last_name
from employees;

Question 8

Write a query to display all records from employees table where the first_name starts with A

Answer 8

Select * from employees
where first_name like ‘A%’;

Question 9

Write a query to display all records from employees table where the first_name ends with A

Answer 9

Select * from employees
where first_name like ‘%A’;

Question 10

Write a Query to create a table EMP_YOURNAME where we have three columns EMP_ID that having int datatype, NAME that having varchar2 datatype and length size is 25, AGE having number datatype that having 3 length size

Answer 10

Create a table EMP_Mukul (EMP_Parth INT
NAME VARCHAR2(25),
AGE NUMBER(3));

Question 11

Write a query to add a extra column in EMP_Parth table, the column is JOB_ID that having varchar2 datatype and length size is 30

Answer 11

Alter table EMP_Parth
ADD JOB_ID varchar2(30);

Question 12

Write a query to drop AGE column from EMP_Parth table

Answer 12

Alter table EMP_Parth
Drop Column_AGE

Question 13

Write a query to rename JOB_ID column to POST_ID from EMP_Parth

Answer 13

Alter table EMP_Parth
Rename column JOB_ID to POST_ID;

Question 14

What is primary key constraint ?

Answer 14

contains unique values and can’t contain NULL values

Question 15

What is an example of primary key ?

Answer 15

Create Table Student_Parth( STD_ID int primary key,
NAME varchar2(30) NULL,
Mobile_Number number(10)
UNIQUE
Salary number (10,2);

Question 16

Regarding constraints with definitions; What is Unique ? What is NOT NULL? What is Check ? What is default ?

Give a table example of check and default

Answer 16

1. Unique: It ensures that all values in columns are different/Unique/Dont have the same data in two rows
2. Not NULL: It ensures that a column satisfies a specific condition.
3. Check : It ensures that the value in a column satisfies a specific condition
   Age column_name
   check(Age >= 18 AND CITY=’CALIFORNIA’)
4. Default : It sets a default value for a column if no value is specified.
   COUNTRY varchar2(30) default ‘INDIA’

Question 17

What is DROP, TRUNCATE, DELETE ?

Answer 17

• Drop: The command is to delete the whole structure of the table
• TRUNCATE: This command is used to delete the data from the existing table
• Delete: Just deleting the row one by one by using a specific condition