sql

Question 1

SQL statement categories

Answer 1

Data definition language
data control language
data manipulation
transaction control
Session control
system control

Question 2

SQL*Plus

Answer 2

environment

oracle proprietary

Question 3

Describe

Answer 3

DESC table_name

DESC table_name

Question 4

data types

Answer 4

varchar2(size)
char(size)
number(p,s)
date
long
clob
raw / long raw
....

Question 5

select * from table_name;

Answer 5

select : what column

from which table

Question 6

sql statements

Answer 6

not case sensitive
one or more lines
keywords cannot be abbreviated or split across lines/

Question 7

select + arithmetic operators

Answer 7

select sal, sal*2 from emp;

Question 8

column alisas

Answer 8

select ename as surname, sal from emp;

Question 9

dual table

Answer 9

single column and single row

do calculation -> see result

Question 10

concatenate

Answer 10

select first_name || ‘ ‘ || last_name AS “Customer Name” from customers;

Question 11

literal Character Strings

Answer 11

literal: char, expression, number in select list
date, char in single quote
select dept || ‘is the number’

Question 12

null value

Answer 12

unknown value
not blank string
special value
important to handle null value

Question 13

check null value

Answer 13

select * from table where dob IS NULL/ IS NOT NULL;

Question 14

distinguish blank string and null

Answer 14

SELECT ename, NVL(com, ‘No commission’) “Commission”

FROM emp;

Question 15

eliminate duplicate rows

Answer 15

select DISTINCT job FROM emp;

Question 16

where

Answer 16

select ename, job, hiredate from emp

where ename = ‘Smith’;

Question 17

not equal

equal

Answer 17

<>

=

Question 18

other comparison operators

Answer 18

between
in
like
is null

Question 19

between

Answer 19

select name, price from table where price between 10 and 15;

Question 20

in

Answer 20

select ename, mgr from emp

where mgr in (7566, 7788)

Question 21

like

Answer 21

select * from table where first_name like ‘C%’;

note: % -> zero or more
_ one char
use escape char like _

Question 22

not

Answer 22

select ename, job from emp

where job NOT IN (‘salemane’, ‘clerk’)

Question 23

soritng

Answer 23

ASC / DESC
select enam, job, sal from emp
order by sal;
select enam, job, sal from emp order by sal desc;

Question 24

sorting by column alias

Answer 24

select ename, sal*12 “Annual Salary” from emp

order by “annual salary”;

Question 25

sorting multi col

Answer 25

select ename, job, sal from emp order by job, sal DESC;