Common sql statements and examples

1. Select statement with Where ... And ... Or

select state, name, salary
from employee
where state='OH' and salary>10000;

Note: you can also use Or instead of And

2. Select statement with Where ... In

select name, state
from employee
where state in ('NY', 'CA');

Note: you can also use "Not in ('NY', 'CA')"

3. Select statement with Where ... Between

select name, salary
from employee
where salary between 10000 and 20000;

Note: you can also use "Not between 10000 and 20000"

4. Select statement with Where ... Like

select name
from employee
where name like 's%';

Note: or '%s%' -- anything before or after 's'

5. Select statement with Sum, Avg, Min, Max

select sum(salary)
avg(salary)
from employee;

Note: you can also use Min or Max to get the minimun value
or maximun value

6. Select statement with Count

select count(*)
from employee

Note: to get the total number of employees

7. Create a table

create table employee
(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),
state varchar(20));

8. Statement with Insert

insert into employee
(first, last, age, address, city, state)
values ('Luke', 'Duke', 45, '2130 Boars Nest',
'Hazard Co', 'Georgia');

9. Statement with Update

update employee
set age = age+1
where first_name='Mary' and last_name='Williams';

10. Statement with Delete

delete from employee
where lastname = 'May';

11. Statement with Drop

drop table employee;

12. Statement with Distinct

SELECT DISTINCT age
FROM employee_info;

Note: distinct return no duplicate records

13. Statement with Group

SELECT max(salary), dept
FROM employee
GROUP BY dept;

Note: This statement will select the maximum salary for the
people in each unique department. Basically, the salary
for the person who makes the most in each department
will be displayed. Their, salary and their department
will be returned.

14. Select statement with * (all)

select * from test

name age country sex
---------- ----------- ---------- ----------
Linda 12 c m
Ray 14 c f
Xun 15 d m
Ray 16 e f

(4 row(s) affected)

15. Select statement with Having

SELECT dept, avg(salary)
FROM employee
GROUP BY dept
HAVING avg(salary) > 20000;

16. Select statement with Order

SELECT employee_id, dept, name, age, salary
FROM employee_info
WHERE dept = 'Sales'
ORDER BY salary desc;

Note: default asc if no

17. Get information from different tables

Example 1:

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;

Example 2:

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;



FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)

SELECT P.ProductID,
S.SupplierID,
S.CompanyName

Example 4:

FROM Suppliers AS S, Products AS P

WHERE S.SupplierID = P.SupplierID //<> not equal

AND P.UnitPrice > $10

AND S.CompanyName LIKE N'F%'

USE pubs


18. Statement with Inner Join

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors AS a INNER JOIN publishers AS p

ON a.city = p.city

AND a.state = p.state

ORDER BY a.au_lname ASC, a.au_fname ASC

Note:

Inner joins return rows only when there is at least one row from both
tables that matches the join condition. Inner joins eliminate the rows that
do not match with a row from the other table. Outer joins, however, return
all rows from at least one of the tables or views mentioned in the FROM clause,
as long as those rows meet any WHERE or HAVING search conditions. All rows are
retrieved from the left table referenced with a left outer join, and all rows
from the right table referenced in a right outer join. All rows from both
tables are returned in a full outer join.

19. Statement with Inner Join

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a LEFT OUTER JOIN publishers p

ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

20. Display user

SQL>show user
user is "SYSTEM"
SQL>

21. The EXIT command terminates the SQL*Plus session and closes the SQL*Plus window. The syntax for the EXIT command is simply EXIT.
22. Create User


SQL> create user tom identified by tom
default tablespace user_data
temporary tablespace temporary_data
quota 1M on user_data
quota 400K on temporary_data;

User created.

23. To allow Tom to connect to the database grant him the create session privilege.

SQL> grant create session to tom;
Grant succeeded.

24. Change the password expire status

SQL> alter user tom password expire;
User altered.

25. Change password

SQL>password;
display: Changing password for tom
Old password: ***
New password: ****
Retype new password: ****
Connected.

26. Lock and unlock account

SQL> alter user tom account lock; //unlock
User altered.

SQL> connect
Enter user-name: tom
Enter password: ****
ERROR:
ORA-2800: the account is locked
Warning: You are no longer connected to Oracle

27. Delete user

drop user user_name [CASCADE]

Note:

The CASCADE option drops all objects in the user's account before dropping
the user, and you must use the CASCADE option if the user has any objects
in the account.

28. Create profile

SQL> create profile sam_user limit

29. To assign a profile to a username that already exists

SQL> alter user sam profile sam_user;
User altered.

30. To assign a profile to a username who does not already exist
1) In this example, you will create a new user with username SALLY and password GUEST. Then you will assign the profile sam_user to SALLY.



SQL> create user sally identified by guest
default tablespace user_data
temporary tablespace temporary_data
profile sam_user;
User created.

2) Grant the create session role to Sally so Sally can log on to the database.

SQL> grant create session to sally;
Grant succeeded.

31. To check that the profile was created. Access the dba_profiles view. This view shows the settings for each profile. At the SQL prompt type:

SQL> select * from dba_profiles
where profile='sam_user';

32. To view the profile assigned to each user in the database:
1. Log into the database as the SYSTEM user. (password: MANAGER)

2. At the SQL prompt type:


SQL> select username, account_status, default_tablespace,
temporary_tablespace, profile
from dba_users
order by username;

Note: The dba_users view displays the user's account status, assigned
default tablespace and temporary tablespace, and profile.

33. Delete profile

drop profile profile_name

34. Query the dba_roles view

SQL> select * from dba_roles;

35. To view which roles are granted to each user or role in the database
1. Log on to the database as the SYSTEM user in SQL*Plus

2. Query the dba_role_privs view


SQL> select grantee, granted_role
from dba_role_privs
order by grantee;

The grantee column is the username or role that has the
ability to use this role.

36. To view the privileges enabled for the current session whether granted directly or via roles

Query the session_privs view
SQL> select * from SESSION_PRIVS;
order by privilege;

37. To view the roles enabled for the current session

Query the session_roles view
SQL> select * from SESSION_ROLES;
order by role;

Note: The session_privs and session_roles views are available to all
users.

37. Create two roles named ASSIST and MANAGER.


SQL> create role assist;
Role created.

38. .Use the connect command to log on to the database as SALLY, whose password is GUEST, to test the MANAGER role.

SQL>connect sally/guest;

39. Revoke the delete privilege from the MANAGER role.

SQL>revoke delete on player from manager;
Revoke succeeded.

40. Delete role

SQL>drop role role_name

41. Create role manager

SQL>create role manager;
Role created.