RDBMS Queries:

1(a).create table college
   (c_id number primary key,
    c_name varchar(20) not null,
    c_city varchar(20));

1(b).create table college
   (c_id number,
    c_name varchar(20) not null,
    c_city varchar(20),
    primary key (c_id));

2(a). create table student
 (s_id number,
  s_name varchar(20) not null,
  s_city varchar(20),
  s_cid number references college(c_id));

2(b).create table teachers
  (t_id number,
  t_name varchar(20) not null,
  t_cid number,
  foreign key (t_cid) references college(c_id));

3(a).create table teachers
  (t_id number,
  t_name varchar(20) not null,
  t_cid number,
  foreign key (t_cid) references college(c_id) on delete cascade);

3(b).create table teachers
  (t_id number,
   t_name varchar(20) not null,
   t_cid number,
   foreign key (t_cid) references college(c_id) on delete not null);

4(a).create table books
  (b_id number primary key,
   b_name varchar(20) unique,
   author varchar(20));

4(b).create table books
  (b_id number primary key,
  b_name varchar(20),
  author varchar(20),
  unique (b_name));

5.alter table books drop primary key;

6(a).create table college
   (c_id number,
    c_name varchar(20) not null,
    c_city varchar(20),
    constraint 'pk1' primary key (c_id));

6(b).create table teachers
  (t_id number,
  t_name varchar(20) not null,
  t_cid number,
  comstraint 'fk1' foreign key (t_cid) references college(c_id));

7(a).CREATE TABLE Persons (
    ID number NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int);

7(b).CREATE TABLE Persons (
    ID number NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18));

7(c).CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Surat');

8(a).commit;
id name
1 Abhi
2 Ankit
4 Pritesh
  INSERT INTO class VALUES(5, 'Rahul');

  UPDATE class SET name = 'Abhijit' WHERE id = '5';

  SAVEPOINT A;

  INSERT INTO class VALUES(6, 'Sinu');

  SAVEPOINT B;

  INSERT INTO class VALUES(7, 'Prince');

  SAVEPOINT C;

  SELECT * FROM class;
id name
1 Abhi
2 Ankit
4 Pritesh
5 Abhijit
6 Sinu
7 Prince

  ROLLBACK TO B;

  SELECT * FROM class;
id name
1 Abhi
2 Ankit
4 Pritesh
5 Abhijit
6 Sinu

9. SELECT SYSTIMESTAMP FROM dual;
    SYSTIMESTAMP
---------------------------------------------------------------------------
14-JUL-19 02.45.41.976000 PM +05:30

10.CREATE TABLE  "SALESDEPARTMENT" 
   (ITEM VARCHAR2(40), 
    SALE NUMBER, 
    BILLING_ADDRESS VARCHAR2(400)
   );

	ITEM	SALE	BILLING_ADDRESS
	laptop	10	15,rang avdhut
	mobile	1	15,happy bunglows
	tablet	2	sumul dairy road
	laptop	2	vesu road
	mobile	6	sudama chawlk

  SELECT item, SUM(sale) AS "Total sales" 
  FROM salesdepartment
  GROUP BY item
  HAVING SUM(sale) < 1000;

  ITEM                                          Total sales
---------------------------------------- -----------
laptop                                            12
tablet                                               2
mobile                                             7


11.SELECT item, SUM(sale) AS "Total sales" 
  FROM salesdepartment
  Order BY item desc;

12.select * from student where s_name like %n%;

13.SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
p_id price
1 11
2 13
3 19

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
  p_id price
5 9
6 23
7 2


14(a).SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'India');

c_id c_name
101 amisha
102 ankit
103 pritesh

14(b).SELECT * FROM Customers
WHERE Country NOt IN ('Germany', 'France', 'India');

c_id c_name
109 vishakha
110 vishv
111 sinu

15(a).SELECT SupplierName
   FROM Suppliers
   WHERE EXISTS
   (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID            AND Price < 20);

SupplierName
ankit
meet
sinu

15(b).SELECT SupplierName
   FROM Suppliers
   WHERE NOT EXISTS
   (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID            AND Price < 20);

SupplierName
pritesh
paras

16.select b.b_id,s.s_name from student "s",(select b_id from book) "b" where b.b_id=s.b_id;

17.select   b_id,s_id,quantity from sales"s" where
  quantity < (select max(quantity) from sales where b_id = s.b_id);

b_id s_id quantity
101 1 1000
103 2 400

18.select s_name from students where (s_class,s_city) IN
(select s_class,s_city from students where s_name='ankit');

s_name
meet
raj
drashti

19.SELECT * FROM Employee order by (select name from branch where e.id=b.id);

eid Name
102 ankit
101 meet
103 raj

20.Aggregate Function
a. SELECT COUNT(p_id) as Cnt FROM products;

Cnt
10

b. select avg(price) as avg from products;

avg
450.50

c. select sum(price) as sum from products;

sum
4500

d. select min(price) as min from products;

min
10

e. select max(price) as max from products;

max
190

21.Numeric Function
a. select abs(-145) from dual;

  ABS(-145)
----------
        145

b. select power(3,2) from dual;

POWER(3,2)
----------
         9

c. select round(3.55,1) from dual;

ROUND(3.55,1)
-------------
          3.6

d. select sqrt(100) from dual;

  SQRT(100)
----------
         10

  e. select exp(100) from dual;

  EXP(100)
----------
2.6881E+43

f. select extract(year from date '2000-11-10') from dual;

EXTRACT(YEARFROMDATE'2000-11-10')
---------------------------------
                              2000

g. select greatest(10,13,50) from dual;

GREATEST(10,13,50)
------------------
                50

h. select least(10,13,50) from dual;

LEAST(10,13,50)
---------------
             10

i. select mod(15,7) from dual;

  MOD(15,7)
----------
         1

j. select trunc(125.258,1) from dual;

TRUNC(125.258,1)
----------------
           125.2

22.mathematical function

a. select floor(20.45) from dual;

FLOOR(20.45)
------------
          20

b. select ceil(24.8) from dual;

CEIL(24.8)
----------
        25

c. select sqrt(100) from dual;

  SQRT(100)
----------
         10

d. select power(3,2) from dual;

POWER(3,2)
----------
         9

23.string function

a. select lower('Ankit Bavadiya') from dual;

LOWER('ANKITBA
--------------
ankit bavadiya

b. select initcap('Ankit Bavadiya') from dual;

INITCAP('ANKIT
--------------
Ankit Bavadiya

c. select upper('ankit bavadiya') from dual;

UPPER('ANKITBA
--------------
ANKIT BAVADIYA

d. select substr('ankit bavadiya',3,4) from dual;

SUBS
----
kit

e. select ascii('a'),ascii('A') from dual;

ASCII('A') ASCII('A')
---------- ----------
        97         65

f. select compose('a'||unistr('\0301')) "comp" from dual;

c
-
ß

g. select decompose(compose('a'||unistr('\0301'))) "decomp" from dual;

de
--
a┤

h. select instr('ankit bavadiya','b') from dual;

INSTR('ANKITBAVADIYA','B')
--------------------------
                         7

i. select translate('ankit bavadiya','aby','123') from dual;

TRANSLATE('ANK
--------------
1nkit 21v1di31

j. select length('ankit bavadiya') from dual;

LENGTH('ANKITBAVADIYA')
-----------------------
                     14

k. select ltrim('ankit bavadiya','a') from dual;

LTRIM('ANKITB
-------------
nkit bavadiya

l. select rtrim('ankit bavadiya','a') from dual;

RTRIM('ANKITB
-------------
ankit bavadiy

m. select trim(' ankit bavadiya  ') from dual;

TRIM('ANKITBAV
--------------
ankit bavadiya

n. select lpad('abcd 1',10,'*') from dual;

LPAD('ABCD
----------
****abcd 1

o. select rpad('abcd 1',10,'*') from dual;

RPAD('ABCD
----------
abcd 1****

p. select vsize('ankit bavadiya') from dual;

VSIZE('ANKITBAVADIYA')
----------------------
                    14


24.conversion function
a. select to_char(12345,'$099,999') from dual;

TO_CHAR(1
---------
$012,345

b. select to_char(pdt,'month DD,YYYY')"n date" from book;

n date
----------------
november 10,2000

25.date conversion function
insert into student(s_id,s_name,jod)
values(101,'ankit',to_date('25-jun-2000','DD-MON-YY'));

26.date function
a. select add_months(sysdate,4) from dual;

ADD_MONTH
---------
12-NOV-19

b. select sysdate,last_day(sysdate) from dual;

SYSDATE   LAST_DAY(
--------- ---------
12-JUL-19 31-JUL-19

c. select months_between('10-nov-2019','10-jan-2000') from dual;

MONTHS_BETWEEN('10-NOV-2019','10-JAN-2000')
-------------------------------------------
                                        238

d. select next_day('10-nov-2018','saturday') from dual;

NEXT_DAY(
---------
17-NOV-18

e. select round(to_date('10-nov-2018'),'yyyy') from dual;

ROUND(TO_
---------
01-JAN-19

f. select new_time(to_date('219/07/01 10:12','yyyy/mm/dd HH24:mi'),'ast','mst') from dual;

NEW_TIME(
---------
01-JUL-19

27.Joins:
a. inner join:
(Ansi style)
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

(theta style)
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders,Customers
where Orders.CustomerID = Customers.CustomerID;

OrderID CustomerName
------------------------------
10248 Wilman Kala
10249 Tradição Hipermercados
10250 Hanari Carnes
10251 Victuailles en stock
10252 Suprêmes délices
10253 Hanari Carnes
10254 Chop-suey Chinese
10255 Richter Supermarkt

b. Left Outer Join:
(Ansi style)
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

(theta style)
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers,orders
Customers.CustomerID=Orders.CustomerID(+);

customername orderID
ankit 1
ankit 2
meet 1
meet 2

c. right Outer Join:
(Ansi style)
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

(theta style)
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers,orders
Customers.CustomerID(+)=Orders.CustomerID;

customername orderID
-----------------------
ankit 1
meet 2
ankit 1
meet 2

d. full Outer Join:
(Ansi style)
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;     

        customername orderID
-----------------------
ankit 1
meet 1
ankit 2
meet 2

e. cross Join:
(Ansi style)
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders full join Customers;
(theta style)
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders,Customers;

customername orderID
-----------------------
ankit 1
meet 1
ankit 2
meet 2

f. self Join:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders,Customers
where Orders.CustomerID = Customers.CustomerID;

OrderID CustomerName
------------------------------
10248 Wilman Kala
10249 Tradição Hipermercados
10250 Hanari Carnes
10251 Victuailles en stock
10252 Suprêmes délices
10253 Hanari Carnes
10254 Chop-suey Chinese
10255 Richter Supermarkt

28.
a. union

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

City
-------
surat
surat
baroda
bombay
chennai

b. intersect:

SELECT City FROM Customers
intersect
SELECT City FROM Suppliers;

City
------
surat

c. minus:

SELECT City FROM Customers
minus
SELECT City FROM Suppliers;

City
------
surat
baroda


29.
     a.simple index:
        CREATE INDEX "IDX1" ON "A_ACL" ("WS_APP_ID");