Scribd
Upload
9 views5 pages

tables for practice

The document outlines SQL commands for creating and managing database tables, specifically for countries, departments, and employees. It includes commands for inserting data, altering table structures, querying data, and creating views. Additionally, it covers various SQL functions and practices related to data retrieval and manipulation.

Uploaded by

rsn.33.66.99
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views5 pages

tables for practice

The document outlines SQL commands for creating and managing database tables, specifically for countries, departments, and employees. It includes commands for inserting data, altering table structures, querying data, and creating views. Additionally, it covers various SQL functions and practices related to data retrieval and manipulation.

Uploaded by

rsn.33.66.99
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 5

CREATE TABLE COUNTRIES(

COUNTRY_ID CHAR(2),
COUNTRY_NAME VARCHAR(40),
REGION_ID NUMBER);

INSERT INTO COUNTRIES VALUES('CA','Canada',2);


INSERT INTO COUNTRIES VALUES('DE','Germany',1);
INSERT INTO COUNTRIES VALUES('UK','United Kingdom',1);
INSERT INTO COUNTRIES VALUES('US','United States of America',2);

CREATE TABLE COUNTRIES(


COUNTRY_ID CHAR(2),
COUNTRY_NAME VARCHAR(40) CONSTRAINT CO_CONAME
REGION_ID NUMBER);

CREATE TABLE COUNTRIES(


COUNTRY_ID CHAR(2),
COUNTRY_NAME VARCHAR(40),
REGION_ID NUMBER);

session 9

CREATE TABLE DEPT(


ID NUMBER(7),
NAME VARCHAR2(25));

INSERT INTO dept


SELECT department_id, department_name
FROM departments;

CREATE TABLE emp


(id NUMBER(7),
last_name VARCHAR2(25),
first_name VARCHAR2(25),
dept_id NUMBER(7));

ALTER TABLE emp


MODIFY (last_name VARCHAR2(50));

SELECT table_name
FROM user_tables
WHERE table_name IN (’DEPT’, ’EMP’);

CREATE TABLE employees2 AS


SELECT employee_id id, first_name, last_name, salary,
department_id dept_id
FROM employees;

DROP TABLE emp;

RENAME employees2 TO emp;

COMMENT ON TABLE dept IS ’Department details’;


COMMENT ON TABLE emp IS ’Employee details’;

SELECT *
FROM user_tab_comments
WHERE table_name = ’DEPT’
OR table_name = ’EMP’;
ALTER TABLE emp
DROP COLUMN FIRST_NAME;

ALTER TABLE emp


DROP UNUSED COLUMNS;

ALTER TABLE emp


add(First_name

select last_name, department_id, salary from employees where job_id not like 'SA\_
%' escape '\' order by last_name asc;
select last_name, department_id, salary from employees where job_id not like 'SA\_
%' escape '\' order by last_name desc;
select last_name, department_id, salary from employees where job_id not like 'SA\_
%' escape '\' order by last_name;
select last_name, department_id, salary from employees where job_id not like 'SA\_
%' escape '\';
select last_name, department_id, salary from employees where department_id is not
null;
select last_name, department_id, salary from employees where department_id is null;
select last_name, department_id, salary from employees where job_id like 'SA\_%'
escape '\';
select last_name, department_id, salary from employees where last_name like '_o%';
select last_name, department_id, salary from employees where last_name like 'K%';
select last_name, department_id, salary from employees where department_id in
(50,60,90);
select last_name, department_id, salary from employees where department_id = 50 or
department_id = 60;
select last_name, salary from employees where salary between 10000 and 20000;
select last_name, salary from employees where salary>=10000 and salary<=20000;

Querying the Data dictionary

1.USER_TABLES

SELECT *FROM TAB;

2. USER_OBJECTS

SELECT *FROM OBJ;

3. USER_CATALOG;

SELECT *FROM CAT;

SELECT TABLE_NAME FROM USER_TABLES;


SELECT DISTINCT OBJECT_TYPE FROM USER_OBJECTS;
SELECT *FROM USER_CATALOG;

SELECT LAST_NAME, SALARY, COMMISSION_PCT, SALARY+(SALARY * COMMISSION_PCT) TOTAL


FROM EMPLOYEES;
SELECT LAST_NAME, SALARY, COMMISSION_PCT, SALARY+(SALARY *NVL(COMMISSION_PCT,0))
TOTAL FROM EMPLOYEES;
SELECT LAST_NAME, SALARY, COMMISSION_PCT, NVL2(COMMISSION_PCT,SALARY+
(SALARY*COMMISSION_PCT),SALARY) TOTAL FROM EMPLOYEES;
SELECT NEXT_DAY(SYSDATE,'FRIDAY') FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;
SELECT ROUND(SYSDATE-10,'MONTH') FROM DUAL;
SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;
SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL;
SELECT HIRE_DATE FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'YYYY') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'MONTH') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'MON') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'M') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'YEAR') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'DD') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'DDSP') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'DDSPTH') FROM EMPLOYEES;
SELECT TO_CHAR(HIRE_DATE,'DDTH') FROM EMPLOYEES;
SELECT T0_CHAR(SALARY,'$9,99,999.99') FROM EMPLOYEES;

Practice:1

2. FALSE
3. TRUE
4. TRUE
5. SAL TO BE USED AS SALARY
* TO BE USED NOT X
, IS MISSING AFTER LAST NAME
"annual salary" format
6. describe departments;
select *From departments;
7 DESCRIBE employees
SELECT employee_id, last_name, job_id, hire_date
FROM employees;

9. select distinct job_id from employees order by job_id asc;


8. select Employee_id,Last_name,Job_id,startdate from employees;
10.SELECT employee_id "Emp #", last_name "Employee",
job_id "Job", hire_date "Hire Date"
FROM employees;
11 SELECT last_name||', '||job_id "Employee and Title"
FROM employees;
12 SELECT employee_id || ',' || first_name || ',' || last_name
|| ',' || email || ',' || phone_number || ','|| job_ id
|| ',' || manager_id || ',' || hire_date || ',' ||
salary || ',' || commission_pct || ',' || department _id
THE_OUTPUT from employees;

Practice 2

1.select last_name,salary from employees where salary>12000;


2.

PRACTICE 10

1. ALTER TABLE emp


ADD CONSTRAINT my_emp_id_pk PRIMARY KEY (id);

2. ALTER TABLE dept


ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);

3. ALTER TABLE emp


ADD (dept_id NUMBER(7));

ALTER TABLE emp


ADD CONSTRAINT my_emp_dept_id_fk
FOREIGN KEY (dept_id) REFERENCES dept(id);

4. SELECT constraint_name, constraint_type


FROM user_constraints
WHERE table_name IN ('EMP', 'DEPT');

5. SELECT object_name, object_type


FROM user_objects
WHERE object_name LIKE 'EMP%'
OR object_name LIKE 'DEPT%';

PRACTICE 11

1. CREATE VIEW employees_vu AS


SELECT employee_id, last_name employee, department_id
FROM employees;

2. SELECT *
FROM employees_vu;

3. SET LONG 200

SELECT view_name, text


FROM user_views;

4. SELECT employee, department_id


FROM employees_vu;

5. CREATE VIEW dept50 AS


SELECT employee_id empno, last_name employee,
department_id deptno
FROM employees
WHERE department_id = 50;

6.

select e.last_name, e.department_id, d.department_name from employees e full outer


join departments d on e.department_id = d.department_id;

select e.last_name, e.department_id, d.department_name from employees e right outer


join departments d on e.department_id = d.department_id;

select e.last_name, e.department_id, d.department_name from employees e left outer


join departments d on e.department_id = d.department_id;

select e.last_name, e.department_id, d.department_name from employees e join


departments d on e.department_id = d.department_id;

select last_name, department_id, department_name from employees join departments


using(department_id);

select last_name, department_id, department_name from employees natural join


departments;

select e.last_name, e.department_id, d.department_name from employees e cross join


departments d;

select e.last_name, e.department_id, d.department_name from employees e,


departments d where e.department_id=d.department_id(+);

select e.last_name, d.department_id, d.department_name from employees e,


departments d where e.department_id(+)=d.department_id;

select e.last_name, e.department_id, d.department_name from employees e,


departments d where e.department_id=d.department_id and e.department_id=90;

select e.last_name, e.department_id, d.department_name from employees e,


departments d where e.department_id=d.department_id;

select e.last_name, e.department_id, d.department_name from employees e,


departments d;

https://www.w3schools.com/sql/sql_primarykey.asp

CREATE TABLE Persons(


ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY(ID)
);

select department_id ,count(department_id) from employees group by department_id


having count(department_id)>3;
select employee_id, job_id from job_history;
select last_name, salary, job_id from employees where salary <all (select salary
from employees where job_id='IT_PROG');
select last_name, salary, job_id from employees where salary >any (select salary
from employees where job_id='IT_PROG');
select last_name, salary, job_id from employees where salary <any (select salary
from employees where job_id='IT_PROG');
select last_name, salary, job_id from employees where salary in (select salary from
employees where job_id='IT_PROG');
select last_name, salary from employees where salary > (select salary from
employees where last_name = 'Davies');

You might also like