SQL QUIERIES

-- TABLE SYNTAX: Create table

Create table worker (srno number, name varchar2(10), age number, department varchar2(10));

-- TABLE SYNTAX: Insertion syntax

Insert into worker values (2, 'charlie', 34, 'HR');

-- TABLE SYNTAX: Altering table syntax

alter table worker modify department varchar2(15);

-- TABLE SYNTAX: creates a 'nickname' for the table employee

create synonym employee1 for emp1;

-- TABLE SYNTAX: creates table identical to worker named employee1

create table employee1 as select * from worker;

-- TABLE SYNTAX: creates a table identical to worker named employee2 that has an imposible mathematical perameter for its filer predicate clause making it empty with the same skeleton

create table employee2 as select * from worker where 1=2;

-- TABLE SYNTAX: removes all data from table 'employee1'

truncate table employee1;

-- TABLE SYNTAX: renames table employee to emp1

rename employee1 to emp1;

-- SEQUENCE SYNTAX: create sequence syntax

create sequence srno_seq increment by 1 start with 1 maxvalue 100 nocycle;

-- SEQUENCE SYNTAX:as this is ddl it automatically commits all peviously executed lines

drop sequence srno_seq;

-- SEQUENCE SYNTAX: identify the next value of a sequence however uses a slot from the sequence

select srno_seq.nextval from dual;

-- SEQUENCE SYNTAX: Identify the current spot in the sequence and presents it in "dua;l table"

select srno_seq.currval from dual;

-- SELECTING VIEWS SYNTAX: shows employee2 table

select * from worker;

-- SELECTING VIEWS SYNTAX: Shows all of table employee (all = *)

select * from worker;

-- SELECTING VIEWS SYNTAX: list out all the workers from different columns (non case sensitive)

select * from worker where lower(department) = 'it';

select * from worker where lower(department) = 'reception';

select * from worker where lower(department) = 'admin';

-- SELECTING VIEWS SYNTAX: Ordering Rows (ASC/DESC)

select * from worker order by srno asc;

select * from worker order by srno desc;

select * from worker order by age asc;

select * from worker order by age desc;

-- SELECTING VIEWS SYNTAX: different ways of showing data ordered by a specific numerical parameters within the data

select * from employee1 order by 1;

select * from emp1 order by 1;

-- SELECTING VIEWS SYNTAX: Shows speicif columns (at least 2

select name, age from worker;

select name, age from worker;

select name, department from worker;

-- SELECTING VIEWS SYNTAX: Controls specific columns and rows (+ ordering it + lower )

select name, age from worker where department = 'Admin' order by srno asc;

select name, age from worker where department in ('Admin', 'manegment');

select name, age from worker where lower(department) in ('Admin', 'manegment', 'it') order by age desc;

-- SELECTING VIEWS SYNTAX: Blacklisting (only works on repeating cell data)

select * from worker where department not in ('Admin');

select name, age from worker where department not in ('Admin');

select name, age from worker where department in ('Admin', 'manegment');

-- SELECTING VIEWS SYNTAX: Creating views

create view worker_admin as select * from worker where lower(department) = 'admin';

-- SELECTING VIEWS SYNTAX: Selecting premade views

select * from worker_admin;

select * from worker_admin where age > 29;

-- SHOWS METADATA FROM THE FOLLOWING TABLES:

describe worker;

describe employee2;

describe employee2;

-- SELECTING LIBRARY THINGS (NEXT LESSON)

desc user_objects;

select object_name , object_type from user_objects;

select * from tab;