ORACLE DBA

DAY:1

Database in collect the information in note pad ,txt ,excl etc..

Database is stored in RAM,CPU etc..

Database is collect the date for names, salaries, information, pictures, videos and anything store in database.

A Database is, basically , a place that we store an organized collection of data. store, access, manipulate, retrieve.

DBMS:

This DBMS type is hierarchical, network, relational, object-relational DBMS.

ORACLE DATABASE:

Oracle database is very secured, performance, scalability,powerful coding and support a company.

DAY:2

Table is used employee date store the union of all these horizontal cell is called as a record or a row.

The table stored the data such that employees ID, Name, salary, phone number, department etc..

Tabular form like in job interviews or exams,etc..

RELATION DATABASE:

If you create two table employees and department table.

To the employees related the department table its relational data base.

Relational database are accurate, flexible, collaborative, trusted, secure.

ENTITY RELATIONAL DATABASE:

The smallest unit is containing a meaningful set of data is called as entity.

Select Statement:

SECTION:32

ORACLE DATA TYES

1.Varchar2: variable length character data.

2.Char: fixed length character data.

3.Number (p,s): varaible length numeric data decimal number. p-precision and s-scale.

4.Date: date and time values.

5.Long: variable-length character data(up to 2GB).

6.Raw and long raw: raw binary data.

7.Blob: maximum size is (4 gigabytes-1) * (DB_BLOCK_SIZE initialization (8TB to 128TD)

8.Clob: maximum size is (4 gigabytes-1) *(DB_BLOCK_SIZE)

9.RowID: a base 64 number system representing the unique address of a row in its table.

SECTION:33

NULL VALUE

*Null value means unknown or nonexistent!

*Null values not enter the in table.

SECTION:34

DESCRIBE COMMAND

EMPOLYEES ID:

SECTION:35

INFORMATION COMMAND:

SECTION:36

INFORMATION COMMAND (CODE):

DESC employees;

INFORMATION employees;

INFO department;

INFO+ employees;

SECTION:37

SQL STATEMENT BASICS:

1.SQL statement are not case sensitive.

2.SQL statement can be separated into multiple lines.

3.Keywords cannot be abbreviated or split.

4. In SQL developer, SQL statement can be terminated by a semicolon ";" or a forward slash "/" sign.

5.The need to be at least one space between the commands.

SECTION:38

SQL STATEMENT BASICS (CODE)

SELECT * FROM employees;

SELECT * FROM department;

SECTION:39

ORACLE ERROR MESSAGE

SECTION:40

ORACLE ERROR MESSAGE (code samples)

select * employees;

select 1 + 'abc' from dual;

select 1 + 2 from dual;

SECTION:41

SELECT STATEMENT

SECTION:42

SELECT STATMENT (CODE)

1. SELECT * FROM employees;

2.SELECT first_name, last_name, email FROM employees;

SECTION:43

USING COLUMN ALIASES

SECTION:44

CODE:USING COLUMN ALIASES

1. SELECT first_name, last_name, email FROM employees;

2. SELECT first_name AS name, last_name as surname, email FROM employees;

3. SELECT first_name as "My name", email "E-mail" FROM employees;

4. SELECT employee_id, salary + nvl (salary * commission_pct,0) + 1000 new_salary FROM employees;

SECTION:45

QUOTE (Q) OPERATOR

SECTION:46

QUOTE (Q) OPERATOR (CODE)

1. select * from dual;

2. select 'my name is adam' as "output" from dual;

3. select 'I''m using quote operator in sql statement' as "output" from dual;

4.select q'[I'm using quote operator in sql statement]' as "quote operator" from dual;

SECTION:47

DISTINCT AND UNIQUE OPERATORS

SECTION:48

DISTINCT AND UNIQUE OPERATORS (CODE)

1. select distinct first_name from employees;

2. select unique first_name from employees;

3. select distinct job_id, department_id from employees;

4. select distinct job_id from employees;

5. select distinct job_id, department_id, first_name from employees;

SECTION:50

CONCATENATION OPERATOR

SECTION:51

CONCATENATION OPERATOR (CODE)

1.SELECT 'my name is alex' from employees;

2.SELECT 'my name is' || first_name from employees;

3.SELECT 'the commision percentage is' || commission_pct as concatenation,comission_pct from employees;

4.select first_name || ' ' || last_name "full name" from employees;

5.select * from locations;

6.select street_address || ',' || city || ',' || postal_code || ',' || state_province || ',' || country_id as

"full address" from locations;

SECTION:52

ARITHMETIC EXPRESSION AND NULL VALUES

SECTION:53

ARITHMETIC EXPRESSIONS AND NULL VALUE (CODE)

1.SELECT * from employees;

2.SELECT employee_id, salary*12 as annual_salary FROM employees;

3.SELECT employee_id, salary, salary+100*12 ass annual_salary FROM employees;

4.SELECT employee_id, salary,( salary+100)*12 ass annual_salary FROM employees;

5.SELECT sysdate FROM dual;

6.SELECT sysdate + 4 FROM dual;

7.SELECT employee_id, hire_date , hire_date+5 from employees;

8.SELECT salary, salary*commission_pct, commission_pct from employees;

SECTION:54

CODING EXERCISE :2 SOLUTION

CODE: SELECT employee_id, salary,salary * 1.2 + 1000 AS new_salary from employees;

SECTION:55

USING WHERE CLAUSE

SECTION:56

USING WHERE CLAUSE (CODE)

1. SELECT * from employees;

2.SELECT * from employees WHERE salary > 10000;

3.SELECT * from employees WHERE job_id = 'IT PROG' ;

SECTION:57

CAMPARISON OPERATORS

COMPARISON OPERATOR DESCRIPTION

= EQUAL

< > NOT EQUAL

!= NOT EQUAL

> GREATER THAN

>= GREATER THAN OR EQUAL

< LESS THAN

<= LESSTHAN OR EQUAL

IN( ) MATCHES AVALUE IN A LIST

NOT NEGATES A CONDITION

BETWEEN WITHIN A RANGE (INCLUSIVE)

IS NULL NULL VALUE

LIKE MATCHING WITH % AND _

SECTION:58

CAMPARISON OPERATOR (CODE)

1.SELECT * from emloyees;

2.SELECT * from emloyees where salary > 1000;

3.SELECT * from emloyees where salary < 1000;

4.SELECT * from emloyees where hire_date = '01-jan-05';

5.SELECT * from emloyees where manager_id = 100;

SECTION:59

BETWEEN...AND OPERATOR

1. Retrieves data between the upper limits and lower limts.

2. The lower and upper limits values are include.

3. Many data types like number, data, character value can used to BETWWEN...AND operator.

SECTION:60

BETWEEN...AND OPERATOR ( CODE SAMPLE)

1.SELECT * from employees WHERE salary BETWEEN 10000 AND 40000;

2.SELECT * from employees WHERE hire_date BETWEEN '7-JUNE-02 ' AND '29-JAN-08';

3.SELECT * from employees WHERE hire_date BETWEEN '7-JUNE-02 ' AND '29-JAN-05';

SECTION:61

IN OPERATOR

SECTION:62

IN OPERATOR (SAMPLE CODE)

1.SELECT * from employees WHERE employee_id IN (50,100,65,210).

2.SELECT * from employees WHERE first_name IN ('steven','peter','adam')

3.SELECT * from employees WHERE hire_date IN ('08-mar-08','30-jan-05').

SECTION:63

LIKE OPERATOR

SECTION:64

LIKE OPERATOR (CODE SAMPLE)

1.SELECT * from employees WHERE job_id= 'SA_REP';

2.SELECT * from employees WHERE job_id LIKE 'SA_REP';

3SELECT * from employees WHERE job_id LIKE 'SA%';.S

4.SELECT * from employees WHERE firdt_name LIKE 'A%';

5.SELECT * from employees WHERE firdt_name LIKE '%a';

6.SELECT * from employees WHERE firdt_name LIKE '%a%';

7.SELECT * from employees WHERE firdt_name LIKE '_r%';

SECTION:65

NULL OPERATOR

SECTION:66

NULL OPERATOR (CODE SAMPLE)

1.select * from employees WHERE commission_pct = NULL;

2.select * from employees WHERE commission_pct IS NULL;

3.select * from employees WHERE commission_pct IS NOT NULL;

SECTION:67

LOGICAL OPERATOR ( OR , AND, NOT)

SECTION:68

LOGICAL OPERATOR(CODE SAMPLE)

SELECT * FROM employees WHERE job_id = 'SA_REP' OR salary > 10000;

SELECT * FROM employees WHERE salary > 10000 AND job_id IN ('SA_MAN', 'SA_REP');

SELECT * FROM employees WHERE salary > 10000 AND job_id NOT IN (SA_MAN','SA_REP);

SECTION:69

RLUES OF PRECEDENCE

SECTION:70

RULES OF PRECENDENCE( SAMPLE CODE)

1.SELECT first_name, last_name, job_id, salary FROM employees WHERE (job_id = 'IT_PROG' or job_id = 'ST_CLERK') and salary > 5000;

2.SELECT first_name, last_name, job_id, salary FROM employees WHERE job_id = 'IT_PROG' or

(job_id = 'ST_CLERK' and salary > 5000);

3.SELECT first_name, last_name, job_id, salary FROM employees WHERE job_id = 'IT_PROG' or job_id = 'ST_CLERK' and salary > 5000;

4.SELECT first_name, last_name, department_id, salary FROM employees WHERE salary > 10000 AND (department_id = 20 OR department_id = 30);

SECTION:72

ORDER BY CLAUSE

SECTION:73

ORDER BY CLAUSE (SAMPLE CODE)

1.SELECT first_name, last_name, salary FROM employees ORDER BY first_name;

2.SELECT first_name, last_name, salary FROM employees ORDER BY last_name;

3.SELECT first_name, last_name, salary, (10*(salary/5) + 3000) - 100 NEW_SALARY FROM employees ORDER BY NEW_SALARY;

4.SELECT first_name, last_name, salary, (10*(salary/5) + 3000) - 100 NEW_SALARY FROM employees ORDER BY 1;

5.SELECT first_name, last_name, salary, (10*(salary/5) + 3000) - 100 NEW_SALARY FROM

employees ORDER BY 2;

6.SELECT *FROM employees ORDER BY first_name, last_name;

7.SELECT *FROM employees ORDER BY first_name, job_id, salary;

SECTION:74

ASC AND DESC OPERATOR

SECTION:75

ASC AND DESC OPERATOR ( CODE SAMPLE)

1.select employee_id, first_name, last_name, salary from employees order by first_name asc;

2.select employee_id, first_name, last_name, salary from employees order by first_name desc;

3.select employee_id, first_name, last_name, salary from employees order by first_name desc, last_name;

4.select employee_id, first_name, last_name, salary from employees order by first_name desc, last_name DESC;

5.select employee_id, first_name, last_name, salary s from employees order by first_name desc, s desc;

6.select employee_id, first_name, last_name, salary s from employees order by 2 desc, s desc;

7.select first_name, salary, commission_pct from employees order by commission_pct;

SECTION:76

NULL FIRST AND NULL LAST OPERATOR

SECTION:77

NULL FIRST AND NULL LAST (SAMPLE CODE)

1.select first_name, salary, commission_pct from employees order by commission_pct;

2.select first_name, salary, commission_pct from employees order by commission_pct NULLS FIRST;

3.select first_name, salary, commission_pct from employees order by commission_pct ASC NULLS FIRST;

4.select first_name, salary, commission_pct from employees order by commission_pct DESC;

5.select first_name, salary, commission_pct from employees order by commission_pct DESC NULLS LAST;

SECTION:78

ROWNUM AND ROWID IN SQL

SECTION:79

ROWNUM AND ROWD IN SQL(SAMPLE CODE)

1.SELECT employee_id,department_id, first_name, last_name, salary, rowid, rownum from employees;

2.SELECT employee_id, first_name, last_name, salary, rowid, rownum from employees where department_id = 60;

3.SELECT employee_id, first_name, last_name, salary, rowid, rownum from employees where department_id = 80;

4.SELECT employee_id, first_name, last_name, salary, rowid, rownum from employees WHERE department_id = 80 and rownum <= 5 order by salary desc;

SECTION:80

ORACLE FETCH CLAUSE

SECTION:81

ORACLE FETCH CLAUSE (CODE SAMPLE)

1.SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC

OFFSET 1 ROW FETCH FIRST 10 ROWS ONLY;

2.SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC

OFFSET 1 ROW FETCH FIRST 10 ROWS WITH TIES;

3.SELECT first_name, last_name, salary FROM employees

OFFSET 1 ROW FETCH FIRST 10 ROWS WITH TIES;

4.SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC

FETCH FIRST 10 ROWS WITH TIES;

5.SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC

OFFSET 5 ROW;

6.SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC

OFFSET 1 ROWS FETCH FIRST 10 ROWS WITH TIES;

7.SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC

OFFSET 1 ROWS FETCH FIRST 10 ROWS WITH TIES;

SECTION:82

SUBSTIUTION VARIABLE

A SUBSTITUTION VARAIBLE IS A USER VARIABLE NAME PRECEDED BY ONE OR TWO APRERSANDS (&).

SECTION:83

SUBSTITUTION VARAIBLE (SAMPLE CODE)

1.SELECT employee_id, first_name, last_name, department_id

FROM employees WHERE department_id = 30;

2.SELECT employee_id, first_name, last_name, department_id

FROM employees WHERE department_id = &department_no;

3.SELECT employee_id, first_name, last_name, department_id

FROM employees WHERE first_name = '&name';

4.SELECT employee_id, first_name, last_name, &column_name FROM &table_name

WHERE &condition ORDER BY &order_by_clause;

SECTION:84

DOUBLE AMPERSAND (&&) AND DEFINE & UNDEFINE COMMANDS

SECTION:85

DOUBLE AMPERSAND (&&) AND DEFINE & UNDEFINE COMMANDS(CODE)

1.SELECT employee_id, first_name, last_name, salary FROM employees

WHERE salary BETWEEN &sal AND &sal + 1000;

2.SELECT employee_id, first_name, last_name, salary FROM employees

WHERE salary BETWEEN &&sal AND &sal + 1000;

3.SELECT employee_id, first_name, last_name, &&column_name FROM employees

ORDER BY &column_name;

4.SELECT &&column_name FROM employees GROUP BY &column_name

ORDER BY &column_name;

DEFINE emp_num = 100;

SELECT * FROM employees WHERE employee_id = &emp_num;

DEFINE emp_num = 200;

SECTION:86

ACCEPT AND PROMPT COMMANDS

*The most reliable and robust methods for getting input from the user is to expilcitly to prompt for values using accept and prompt command.

*The accept command takes input from the user and store it in a user variable.

*The prompt command is used to display the messages to the user for supplying a brief explanation of what your script is going to accomplish.

SECTION:87

ACCEPT AND PROMPT COMMANDS(CODE)

1.ACCEPT emp_id PROMPT 'Please Enter a valid Employee ID:';

1.SELECT employee_id, first_name, last_name, salary FROM employees

WHERE employee_id = &emp_id;

1.UNDEFINE emp_id;

2.ACCEPT min_salary PROMPT 'Please specify the MINIMUM salary:'

ACCEPT max_salary PROMPT 'Please specify the MAXIMUM salary:'

SELECT employee_id, last_name, salary FROM employees

WHERE salary BETWEEN &min_salary AND &max_salary;

2.UNDEFINE min_sal;

2.UNDEF max_sal;

SECTION:88

SET VERIFY ON-OFF COMMAND

*The verify command displays that status of variable before and after the substitution.

*The set verify command is used to two ways which are:

* SET VERIFY ON; -TRUNS ON VERIFICATION

* SET VERIFY OFF; -TRUNS OFF VERIFICATION

SECTION:89

SET VERIFY ON-OFF COMMAND (CODE)

1.SELECT employee_id, first_name, last_name, department_id

FROM employees WHERE department_id = &dept_id;

SET VERIFY ON;

SET VERIFY OFF;

2.SELECT * FROM departments WHERE department_name = 'R&D';

SET DEFINE OFF;

SET DEFINE ON;

TABLE CREATION AND INSERT:

CREATE TABLE departments5 (department_id NUMBER(2),department_name VARCHAR2(20),location VARCHAR2(13));

select * from departments5;

INSERT INTO departments5 (department_id,department_name,location) VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO departments5 (department_id,department_name,location) VALUES (20,'RESEARCH','DALLAS');

INSERT INTO departments5 (department_id,department_name,location) VALUES (30,'SALES','CHICAGO');

INSERT INTO departments5 (department_id,department_name,location) VALUES (40,'OPERATIONS','BOSTON');

COMMIT;

CREATE TABLE employees5(employee_id NUMBER(4),employee_name VARCHAR2(20),job VARCHAR2(9),manager_id NUMBER(4),hiredate DATE,

salary NUMBER(7,2), commission NUMBER(7,2),department_id NUMBER(2));

select*from employees5;

1.INSERT INTO employees5 (employee_id,employee_name,job,manager_id,hiredate,salary,commission,department_id)

VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

2.INSERT INTO employees5(employee_id,employee_name,job,manager_id,hiredate,salary,commission,department_id)

VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

3.INSERT INTO employees5(employee_id,employee_name,job,manager_id,hiredate,salary,commission,department_id)

VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

commit;

CREATE TABLE suppliers5(supplier_id number(4),supplier_name varchar2(50));

select * from suppliers5;

INSERT INTO suppliers5 (supplier_id,supplier_name) VALUES (1000,'apple');

INSERT INTO suppliers5 (supplier_id,supplier_name) VALUES (2000,'mango');

INSERT INTO suppliers5 (supplier_id,supplier_name) VALUES (3000,'grape');

INSERT INTO suppliers5 (supplier_id,supplier_name) VALUES (4000,'guava');

commit;

CREAT TABLE AND INSERT THE VALUE AND NULL

create table customer(cust_id number(6),cust_name varchar2(50),mobile_no number(10),

dob date,city varchar2(30),email_id varchar2(30

select * from customer;

insert into customer

values(1000,'mani',1234567890,to_date('12/02/2023','mm/dd/yyyy'),'delhi','manisarathi@gmail.com');

NULL

create table customer(cust_id number(6),cust_name varchar2(50),mobile_no number(10),

dob date,city varchar2(30),email_id varchar2(30

select * from customer;

insert into customer (cust_id,cust_name,dob,city,email_id)

values(1000,'mani',to_date('12/02/2023','mm/dd/yyyy'),'delhi','manisarathi@gmail.com');

ROLLBACK:

create table customer(cust_id number(6),cust_name varchar2(50),mobile_no number(10),

dob date,city varchar2(30),email_id varchar2(30

select * from customer;

iinsert into customer values(1000,'mani',1234567890,to_date('12/02/2023','mm/dd/yyyy'),'delhi','manisarathi@gmail.com');

rollback;

UPDATE NULL VALUE IN THE COLUMN:

--update record

update customer set mobile_no=6372453672

rollback;

update customer set mobile_no=6372453672 where cust_id=1000

commit;

ADD COLUMN AND UPDATES VALUES:

--add column

alter table customer add country varchar2(20)

SELECT * from customer;

--add column

alter table customer add country varchar2(20)

SELECT* FROM CUSTOMER;

update customer set country='india';

commit;

--drop column:

select * from customer;

--drop a column

alter table customer

drop column city;

modify datatypes:

alter table customer

modify cust_id number(10);

desc customer;

TRUNCATE

select * from customer;

truncate table customer;

BACK UP THE TABLE:

SELECT * from suppliers5;

create table suppliers5_bkp as select * from suppliers5;

select *from suppliers5_bkp;

DELETE THE TABLE:

select *from suppliers5_bkp;

delete from suppliers5_bkp;

ROLLBACK;

delete from suppliers5_bkp where supplier_id=1000 ;

RENAME :

DROP Table suppliers5;

rename suppliers5_bkp to suppliers5;

SELECT * from suppliers5;

SELECT * from suppliers5;

ALTER TABLE suppliers5 rename column supplier_name to fruit_name;

SAVE POINT;

creat table test (no1 number(3),no2 number(3));

insert into test values (1,2);

savepoint a;

insert into test values (3,4);

savepoint b;

insert into test values (5,6);

savepoint c;

insert into test values (7,8);

savepoint d;

select * from test ;

rollback to b;

PRIMARY KEY:

create table customers4

(cust_id number(6)primary key,cust_name varchar2(30)not null,

mobile_no number(10)unique check(length(mobile_no)=10),

age number(3)check(age>=18),city_id number(4)references city4(city_id));

desc customers4

create table city4(city_id number(4),city_name varchar2(30));

insert into city4 values(10,'chennai');

insert into city4 values(20,'delhi');

insert into city4 values(30,'kpm');

insert into city4 values(40,'hyd');

commit;

desc city4;

alter table city4 add primary key(city_id);

select * from all_cons_columns where owner='HR' AND table_name='city4';

1.Select statement.

2.where clause.

3.rownum,rowid.

4.is null, is not null.

5.to_char.

6.count(*), count(1) function.

7.pattern matching (LIKE keyword).

8.order bu clause.

9.upper,lower,reverse,length function.

10.column concatenation,column alias,calculation.

SELECT STATEMENT:

COLUMN ALIAS;

select * from employees;

select employee_id,first_name,job_id,email,department_id from employees;

--column alias

select employee_id as associate_id,first_name as worker_name,job_id,email,salary,department_id from employees;

COLUMN CONCATENATION:

select employee_id,first_name,concat(first_name,last_name),job_id,email,
hire_date,department_id from employees;

select employee_id,first_name,last_name,first_name||' '||last_name fullname,job_id,email,

hire_date,department_id from employees;

COLUMNB CALCULATION:

--column calculation

select employee_id,first_name,job_id,email,salary,salary +1000 new_salary,department_id from employees;

UNIQUE:

--DISTINCIT

SELECT distinct department_id from employees;

--COUNT

SELECT COUNT(department_id)from employees;

UNIQUE DEPATMENT_ID;

SELECT COUNT(distinct department_id) from employees;

ROWNUM AND ROWID

select rownum,rowid from employees;

select rownum,rowid, e.* from employees e;

WHERE CLAUSE:

SELECT * from employees where salary >=5000 and salary<=7000;

SYSDATE AND TIME:

SELECT SYSDATE from employees;

SELECT SYSTIMESTAMP from employees;

SUBSTRING:

select substr('welcome to india!!!',12,5) from dual;

select substr('welcome to india!!!',-8) from dual;

select substr('welcome to india!!!',-8,5) from dual;

select job_id,substr(job_id,1,4) from employees;

INSTR:

select instr('corporate floor','ra')from dual;

select instr('corporate floor','ra',1,2)from dual;

LPAD AND RPAD:

select LPAD('welcome',15,'*')from dual;

select RPAD('welcome',15,'*')from dual;

select LPAD(RPAD('welcome',8,'*'),10,'*')from dual;

select SALARY, LPAD(salary,15,0)from employees;

LTRIM AND RTRIM

SELECT LTRIM(' welcome') from dual;

SELECT RTRIM(' welcome') from dual;

SELECT LTRIM('00000123456','0') from dual;

REPLACE:

select replace('jack and jue','j','bl') from dual;

select phone_number,replace(phone_number,'.',null) from employees;

ROUND:

select round(0.7) from dual;

select round(13.67) from dual;

select round(5434.7878) from dual;

select round(5436.7878,2) from dual;

TRUNC:

select trunc(5436.7878,2) from dual;

AGGREGATE FUNCTION:

1.MIN ( ) .

2.MAX ( ).

3.SUM ( ).

4.COUNT ( ).

5.AVG ( ).

select min(salary) from employees;

select max(salary) from employees;

select sum(salary) from employees;

select count(salary) from employees;

select round(avg(salary),2) from employees;

SECTION:91

FUNCTION :CASE CONVERSION

1.LOWER CASE: Convert all the input characters to lowercase characters.

2.UPPER CASE: Convert all the input characters to uppercase characters.

3.ITINCAP : Convert to first letter of each word to uppercase and rest in the lowercase.

SECTION:92

1.SELECT first_name, UPPER(first_name), last_name, LOWER(last_name),

email, INITCAP(email) FROM employees;

SELECT first_name, UPPER(first_name), last_name, LOWER(last_name),
email, INITCAP(email) FROM employees WHERE job_id = 'IT_PROG';

SELECT first_name, UPPER(first_name),last_name, LOWER(last_name),
email, INITCAP(email), UPPER('bmw i8')FROM employees
WHERE job_id = 'IT_PROG';