- ROWID AND ROWNUM
- It IS an address FROM the memory.
- SQL> SELECT employee_id,first_name, salary, ROWID, ROWNUM FROM hr.employees;
- SQL> SELECT employee_id,first_name, salary, ROWID, ROWNUM FROM hr.employees WHERE employee_id=198;
- DATE FUNCTION AND TRUNCATE
- SQL> SELECT employee_id,hire_date,SYSDATE,TRUNC((sysdate-hire_date)/365) AS No_Of_Years FROM hr.employees WHERE ROWNUM <= 10;
- GROUP BY FUNCTION
- GROUP BY prevents repeating the same VALUE
- SQL> SELECT department_id, SUM(salary) AS total_salary FROM hr.employees GROUP BY department_id;
- Creating USER IN a DATABASE
- SQL> CREATE USER szabist IDENTIFIED BY szabist;
- SQL> conn szabist/szabist@localhost:1521/XEPDB1;
- Granting ACCESS
- SQL> GRANT CONNECT TO szabist;
- SQL> GRANT RESOURCE,dba TO szabist;
- GRANT unlimited TABLESPACE TO szabist;
- Revoking ACCESS
- REVOKE
- TO ACCESS: SQL> conn szabist/szabist@localhost:1521/XEPDB1;
- CREATE TABLE
- CREATE TABLE tablename
- (col1 datatype CONSTRAINT,
- col2 datatype CONSTRAINT,
- col3 datatype CONSTRAINT);
- SQL> CREATE TABLE customers
- 2 (
- 3 customer_id NUMBER(5) NOT NULL,
- 4 customer_name VARCHAR(30) NOT NULL,
- 5 customer_city VARCHAR(30) NOT NULL
- 6 );
- TABLE created.
- Inserting INTO TABLE
- SQL> INSERT INTO customers (customer_id, customer_name, customer_city) VALUES (101,'John','Karachi');
- TO UPDATE the TABLE
- SQL> UPDATE customers SET customer_city = 'Munich' WHERE customer_id=101;
- TO DELETE ROW IN the TABLE
- SQL> DELETE FROM customers WHERE customer_id=103;
- TO make a duplicate OF the TABLE
- SQL> CREATE TABLE consumer (consumer_id, consumer_name, consumer_city) AS SELECT customer_id,customer_name,customer_city FROM customers;
- ADD COLUMN IN the TABLE
- SQL> ALTER TABLE consumer ADD consumer_address VARCHAR(20);
- Renaming columns
- SQL> ALTER TABLE consumer RENAME COLUMN consumer_address TO consumer_addressr;
- TO DROP the COLUMN
- SQL> ALTER TABLE consumer DROP COLUMN consumer_addressr;
Recent Pastes