DBMS1.sql

CREATE DATABASE database_name;

CREATE TABLE table_name(
	col1 data_type1,
	col2 data_type2
);

CREATE TABLE student(
	USN char(10),
	Name char(30)
);

CREATE TABLE person(
	personid int,
	lastname varchar(255),
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
);

varchar, char, int, date, timestamp, boolean

CREATE table bms.person(
);

CREATE TABLE person(
	ID int NOT NULL,
	FirstName varchar(255) NOT NULL,
	LastName varchar(255) NOT NULL,
	Age int	
);

CREATE TABLE Person(
	ID int NOT NULL,
	LastName varchar(255),
	Age int,
	CHECK(Age>=18)
);

CREATE TABLE Person(
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Age int,
	City varchar(255) DEFAULT 'Bangalore'
);

CREATE TABLE Person(
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
	Age int,
	PRIMARY KEY(ID)
);

CREATE TABLE Person(
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
	Age int,
	CONSTRAINT PK_PERSON PRIMARY KEY(ID, LastName)
);

CREATE TABLE Student(
	USN char(10),
	Name char(20),
	Dep_Num int,
	PRIMARY KEY(USN),
	FOREIGN KEY(Dep_Num) REFERENCES Dept(D_ID)
);

CREATE TABLE Dept(
	D_ID int,
	D_Name char(3),
	PRIMARY KEY(D_ID)
);

INSERT INTO table_name
VALUES (val1, val2, val3);

INSERT INTO Customer(ID, NAME, AGE, ADDRESS, SAL)
VALUES(1, 'Ramesh', 32, 'Banaglore', 2000.0);

INSERT INTO Student
VALUES ('1BM17CS001'. 'Kayal');

DELETE FROM table_name WHERE condition;

WHERE
OR
AND
NOT

=
<> !=
<
>
>=
<=

DELETE FROM table_name;

UPDATE table_name
SET col1=val1, col2=val2
WHERE condition

UPDATE Customer
SET ContactName = 'Kayal', City='Chennai'
WHERE Country;

CREATE TABLE Cars(
	car_id Number PRIMARY KEY,
	CONSTRAINT fk_brand
	FOREIGN KEY(brand_id)
	REFERENCES (brand_ud) ON DELETE CASCADE
);



What is a foreign key with "Set NULL on Delete" in Oracle?

A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.

A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.


CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE SET NULL
);


ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name)
  ON DELETE SET NULL;



What is a foreign key with Cascade DELETE in Oracle?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in Oracle.

A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.


CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE CASCADE
);

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name)
  ON DELETE CASCADE;



  ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

ALIAS


SELECT contact_id, first_name || last_name AS NAME
FROM contacts
WHERE last_name = 'Anderson';

OR use "NAME"
FROM products p

ORDER BY p.product_name ASC, categories.category_name ASC;


SELECT TOP 3 * FROM Customers
WHERE Country='Germany';

 SELECT TOP 50 PERCENT * FROM Customers; 


 SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3; 


BETWEEN

WHERE customer_id NOT BETWEEN 3000 AND 3500;


SELECT *
FROM order_details
WHERE order_date BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');

WHERE supplier_name LIKE 'Sm_th';
% *
_ .

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 25000;

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary < 49500
GROUP BY department
HAVING COUNT(*) > 10;

WHERE customer_name IN ('IBM', 'Hewlett Packard', 'Microsoft');


ALTER TABLE table_name
ADD col1 datatype;


ALTER TABLE table_name
DROP COLUMN col1;

ALTER TABLE table_name
MODIFY col1 datatype;

ALTER TABLE table_name
MODIFY COLUMN col1 datatype; prior to 10G



NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
INDEX

CONSTRAINT uc UNIQUE(ID, LastName)
CONSTRAINT ck CHECK(AGE>18 AND City='Bangalore')
ORDERDATE DATE DEFAULT GETDATE()


ALTER TABLE person
MODIFY City DEFAULT 'Sadness';

ALTER TABLE Person
ALTER COLUMN City DROP DEFAULT;

CREATE SEQUENCE seq 
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

INSERT INTO Person(ID, FirstName, LastName) VALUES (seq.nextval, 'A', 'K');

SECOND HIGHEST MARKS?

SELECT MAX(marks)
FROM Student
WHERE marks != (SELECT MAX(marks) from Student);

Select EmpName
FROM mpsal e1
WHERE 2 = (SELECT COUNT(e2.Salary) FROM empsal e2 WHERE e2.Salary>=e1.salary);

ALL
ANY
IN
SOME

WHERE comparison-operator ALL

WHERE ProductID = ANY(SELECT Product ID FROM OrderDetails WHERE Quantity =10);

SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ANY(SELECT e2.sal FROM empe2 WHERE d2.deptno = 10);

CREATE VIEW view_name AS
SELECT col1, col2
FROM table_name
WHERE condition;

CREATE VIEW dep_info(Dept_name, Num_of_Emps, Total_Salary) AS
SELECT d.Dname, count(*), sum(salary)
FROM department d, faculty f
WHERE d.Dnumber = f.Dnumber
GROUP BY d.Dname


UPDATING VIEW

CREATE VIEW cs_Faculty_view(F_ID, F_name, Dnum, EMail_ID) AS
SELECT *
FROM faculty
WHERE Dnum = 10;

DROP VIEW view_name;

Advantages of view:
1. Security
2. Query Simplicity
3. Structural Simplicity
4. Consistency
5. Data Integrity
6. Logical Data Independence

Disadvantages of views:
1. Performance
2. Update restrictions


CREATE ASSERTION SunLoans CHECK(
	100000 >= ALL
	SELECT SUM(amount)
	FROM borrower B, loan L 
	WHERE B.loan_number = L.loan_number
	GROUP BY customer_name
);

CREATE ASSERTION NumAccounts CHECK(
	2>= ALL
	SELECT COUNT(*)
	FROM account A, depositor D  
	WHERE A.accountNumber = D.accountNumber
	GROUP BY customer_name, branch_name
);

CREATE ASSERTION CityCheck CHECK(
	NOT EXISTS(
	SELECT *
	FROM customer 
	WHERE cutomer_city IS NULL)
);

TRUNCATE TABLE table_name;

drop table department cascade constraints;
Department Table will be deleted and foreign key constraint to student
Table will be dropped