Notes

Antoni, Abacki, 2000
Bartosz, Babacki, 3000
Cezary, Cabacki, 4000
Dominik, Dadacki, 6000
Eustachy, Ebacki, 2000
Franciszek, Fabacki, 2500
Grzegorz, Gagadzki, 4500
Henryk, Habacki, 5000


CREATE TABLE Offices
(state CHAR(2),
street_number CHAR(30),
city CHAR(20),
zipcode CHAR(8),
phone CHAR(15),
email CHAR(30),
website CHAR(30),
office_no NUMBER(3) PRIMARY KEY,
FOREIGN KEY (state) REFERENCES States)

ALTER TABLE States
ADD CONSTRAINT NN_capitalcity NOT NULL (capitalcity)

ALTER TABLE States
ADD CONSTRAINT CHECK_population CHECK (amount>=0) (population)

ALTER TABLE States
ADD CONSTRAINT UQ_name UNIQUE (name)

CREATE TABLE Offices
(salesman_id NUMBER(8) PRIMARY KEY,
surname_name CHAR(25) NOT NULL,
security_number CHAR(11) UNIQUE,
date_of_birth DATE,
zip CHAR(30),
superviser_id NUMBER (8),
phone_number CHAR (10),
email CHAR(30),
office_no NUMBER(3) FOREIGN KEY,
city CHAR (20),
street_and_no CHAR (30),
FOREIGN KEY (state) REFERENCES States)

CREATE TABLE Offices
(salesman_id NUMBER(8) PRIMARY KEY,
surname_name CHAR(25) NOT NULL,
security_number CHAR(11) UNIQUE,
date_of_birth DATE,
zip CHAR(30),
superviser_id NUMBER (8),
phone_number CHAR (10),
email CHAR(30),
office_no NUMBER (3),
state CHAR (2),
city CHAR (20),
street_and_no CHAR (30),
FOREIGN KEY (office_no) REFERENCES Offices,
FOREIGN KEY (state) REFERENCES States)

CREATE TABLE Languages
(language_id CHAR (3) PRIMARY KEY,
language_name CHAR (16) UNIQUE)






cursor must be declaed it is an interface between

forms of databases

decryption

changing passwords:

ALTER USER user_name
IDENTIFIED BY new_password

changing language to english:

ALTER SESSION
SET NLS_LANGUAGE= 'ENGLISH'

SELECT *
FROM user_tables

SELECT *
FROM all_tables

creating tables:

CREATE TABLE STATES
(state CHAR(2),
name CHAR(16),
capitalcity CHAR(20),
population NUMBER(6,2))

adding data :

INSERT INTO STATES VALUES
('NY','NEW YORK','ALBANY','1954,20')

opening a table:

SELECT *
FROM STATES

deleting an attribute:

DELETE FROM STATES
WHERE POPULATION=2130

updating an attribute:(virgul yerine nokta koymak gerekiyor)

UPDATE STATES
SET POPULATION=1954.21
WHERE STATE='NY'

table rename:

RENAME STATES TO STATES_OLD

creating table with integrity constraints:

CREATE TABLE STATES
(state CHAR(2) PRIMARY KEY,
name CHAR(16) UNIQUE,
capitalcity CHAR(20) NOT NULL,
population NUMBER(6,2) CHECK(POPULATION>=0))

ADD ATTRIBUTES FROM OLD TABLE TO A NEW ONE :
INSERT INTO STATES
SELECT * FROM STATES_OLD

adding attribute:

ALTER TABLE STATES
ADD AREA NUMBER(7,1)

EX for offices attribute:
CREATE TABLE OFFICES
(state CHAR(2) ,
street_and_number CHAR(30),
zipcode CHAR(8),
phone CHAR(15),
email CHAR(30),
website CHAR(30),
office_number NUMBER(3) PRIMARY KEY,
FOREIGN KEY(state) REFERENCES STATES)

INSERT INTO OFFICES VALUES
('NY','BROOK ST 45','4584','0554869524','[email protected]','www,office1.com','233')

EX for salesman:
CREATE TABLE SALESMAN
(SALESMAN_ID NUMBER(8) PRIMARY KEY,
SURNAME_NAME CHAR(25) NOT NULL,
SECURITY_NUMBER CHAR(11) UNIQUE,
DATE_OF_BIRTH DATE,
ZIP CHAR(30),
SUPERVISOR_ID NUMBER(8),
PHONE_NUMBER CHAR(10),
EMAIL CHAR(30),
OFFICE_NUMBER NUMBER(3),
CITY CHAR(20),
STREET_AND_NUMBER CHAR(20),
STATE CHAR(2),
FOREIGN KEY (OFFICE_NUMBER) REFERENCES OFFICES,
FOREIGN KEY (STATE) REFERENCES STATES )

INSERT INTO SALESMAN VALUES
('001','ASILIOGLU BORA YIGIT','10000000','1995.01.01','2345','10000000','5050651735','[email protected]','233','NEW YORK CITY','WALLACE STREET 55','NY')

CREATE TABLE KNOWS
(SALESMAN_ID NUMBER(8),
LANGUAGE_ID CHAR(3),
LEVEL_OF_LANG CHAR(2) CHECK (LEVEL_OF_LANG in ('A','B','C')),
PRIMARY KEY(SALESMAN_ID,LANGUAGE_ID),
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN,
FOREIGN KEY(LANGUAGE_ID) REFERENCES LANGUAGES)

CREATE TABLE INVOICES
(INVOICE_NUMBER CHAR(12) PRIMARY KEY,
INVOICE_DATE DATE,
CUSTOMER_ID NUMBER(8),
SALESMAN_ID NUMBER(8),
INVOICE_VALUE NUMBER(9,2) CHECK(INVOICE_VALUE>=0),
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN,
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS)

CREATE TABLE INV_DETAILS
(ID CHAR(9),
QUANTITY NUMBER(10),
VALUE NUMBER(9,2),
INVOICE_NUMBER CHAR(12),
LINE_NO NUMBER(3),
FOREIGN KEY (ID) REFERENCES PRODUCTS,
FOREIGN KEY (INVOICE_NUMBER) REFERENCES INVOICES,
PRIMARY KEY(INVOICE_NUMBER,LINE_NO))

INSERT INTO INVOICES
VALUES ('9753','2019.01.03','8865','1','555300')

SELECT ONO, STATE, CITY
FROM RK0.OFFICES

ALFABETIK SEHIRLERI SIRALAMAK(CUSTOMER'DAN)

SELECT CITY
FROM RK0.CUSTOMER ORDER BY CITY

SELECTING DISTINCTIVELY

SELECT DISTINCT CITY
FROM RK0.CUSTOMER

SELECT COMPANY,STATE
FROM RK0.CUSTOMER
WHERE STATE='TX'

SELECT COMPANY,STATE
FROM RK0.CUSTOMER
WHERE STATE IN ('TX','FL')

SAME:

SELECT COMPANY,STATE
FROM RK0.CUSTOMER
WHERE STATE IN ('TX','FL','AL','NM','AZ')


SELECT COMPANY
FROM RK0.CUSTOMER
WHERE COMPANY LIKE '%Computer%'

SELECT COMPANY
FROM RK0.CUSTOMER
WHERE COMPANY LIKE '__x%'

NUMBER OF CUSTOMER

SELECT COUNT(ONO)
FROM RK0.CUSTOMER

SELECT COMPANY,YTDPURCH
FROM RK0.CUSTOMER
WHERE
YTDPURCH>=(SELECT MAX(YTDPURCH)
FROM RK0.CUSTOMER )

SELECT STATE,SUM(YTDPURCH)
FROM RK0.CUSTOMER
GROUP BY STATE

SELECT STATE, SUM(YTDPURCH) ,COUNT(CNO)
FROM RK0.CUSTOMER
GROUP BY STATE
HAVING COUNT(STATE) >= 10
ORDER BY COUNT(CNO) DESC



CREATE VIEW ActiveSailors (name,age,rating)
AS SELECT DISTINCT S.name, S.age , S.rating
FROM Sailor S , Reserves R
WHERE S.sname=R.sname AND S.rating >=6

GRANT INSERT,DELETE ON STATES TO Adam WITH GRANT OPTION


SELECT NAME, SALESMAN.SALESMAN, SUM(ITOTAL), COUNT(ITOTAL)
FROM RK0.SALESMAN , RK0.INVOICES
WHERE SALESMAN.SALESMAN = INVOICES.SALESMAN
GROUP BY NAME,SALESMAN.SALESMAN

SELECT O.ONO, O.CITY, SUM(ITOTAL), COUNT(ITOTAL)
FROM RK0.OFFICES O , RK0.INVOICES I , RK0.SALESMAN S
WHERE O.ONO= S.ONO AND S.SALESMAN= I.SALESMAN
GROUP BY O.ONO, O.CITY


SELECT O.ONO, O.CITY, SUM(ITOTAL), COUNT(ITOTAL)
FROM RK0.OFFICES O , RK0.INVOICES I , RK0.SALESMAN S
WHERE O.ONO= S.ONO AND S.SALESMAN= I.SALESMAN
GROUP BY O.ONO, O.CITY

SELECT NAME, S.CITY,O.CITY, S.SALESMAN
FROM RK0.OFFICES O , RK0.SALESMAN S
WHERE O.CITY= S.CITY

SELECT NAME, S.CITY,O.CITY, S.SALESMAN
FROM RK0.OFFICES O , RK0.SALESMAN S
WHERE O.ONO=S.ONO AND NOT O.CITY=S.CITY

SELECT S.SALESMAN , NAME , P.PNO, P.DESCRIPT,SUM(QTY),SUM(LTOTAL)
FROM RK0.PARTS P,RK0.DETAIL D,RK0.INVOICES I,RK0.SALESMAN S
WHERE S.NAME LIKE 'Jay Kennedy%' AND S.SALESMAN=I.SALESMAN AND I.INO=D.INO AND D.PNO=P.PNO
GROUP BY S.SALESMAN,NAME,P.PNO,P.DESCRIPT

select customer.cno, sum(detail.qty), sum(detail.ltotal),customer.company

from rk0.customer, rk0.parts,rk0.detail, rk0.invoices

where parts.descript like '%Woodyard lizard%' and parts.pno=detail.pno and detail.ino=invoices.ino and invoices.cno=customer.cno

group by customer.cno, customer.company

having sum(detail.qty)>1