Notes

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