Getting Started with SQL

Create user Xianxian identified by Xianxian
--dcl
Grant connect to Xianxian-Connect login role
Grant resource to Xianxian--resource operational Data permissions
Grant create any view to Xianxian--permissions to create views for users
More than---Logged in as a system administrator (DBA operation)

--Build table structure data define LANGUAGE--DDL
CREATE TABLE MyUser
(--Field
Username VARCHAR2 (12),
Password VARCHAR2 (12)
)

Select U.username from MyUser u

CREATE TABLE Department
(
Departid int PRIMARY KEY,
Departname VARCHAR2 (12),
Remark VARCHAR2 (12)
)
--crud (technical terminology for increased deletion and inspection)--DML
-Increase
INSERT INTO department (Departid,departname,remark) VALUES (100, ' Department of Personnel ', ' very powerful ')
INSERT INTO department (Departid,departname,remark) VALUES (101, ' Tech ', ' Bull's ')
--Modification
Update Department T set t.departname= ' software department ' where t.departname= ' personnel '
--Delete
Delete from department t where t.departid=100;
--Query
SELECT * FROM department



Sql--standard Query Language standard queries language
--dml
--ddl
--dcl


--Create Sequence object, starting from 100, self-growth, increment by 1, no maximum, no loop
Create sequence seq_department increment by 1 start with Nomaxvalue nocycle

-Requires pseudo-table dual
Select Seq_department. Nextval from dual--first executes nextval, which is defined in this reply
Select Seq_department. Currval from dual
--The rollback of things reflects the consistency of the data and facilitates the maintenance of the data.
INSERT INTO department (departid,departname) VALUES (seq_department. Nextval, ' design Department ')
SELECT * FROM department


drop table employee;
CREATE TABLE Employee (
Empid Number primary Key,
EmpName VARCHAR2 (15),
Salary number (5,2),--Integer plus decimal part up to 5 bits, decimal place two bits
HireDate Date,
DeptID number
)
SELECT * from employee;
INSERT into employee values (7, ' Weapons Master ', null,sysdate,112);
Select Deptid,sum (Salary), round (AVG (NVL (salary,0)), 2), COUNT (*) from the employee group by DeptID ORDER by AVG (NVL (salary,0) ) desc;
--distinct single (de-weight)
---NVL () causes the grouping function to ignore null values--AVG (NVL (salary,0))
--% represents any number of characters--%req%: wildcard character req as substring
--to_date (' 1993-06-18 ', ' yyyy-mm-dd ')--the string is converted into a date type for adding data
--to_char (hiredate, ' mm-dd-yyyy ')---Conversion of a date type to a string for querying

Select To_char (sysdate, ' dd-mm-yyy ') from dual

--Sort the strings according to the ASCII table
SELECT * FROM Employee ORDER BY empname Desc--Descending
SELECT * FROM Employee ORDER by Salary,hiredate--Ascending

--Used in a group by select can not appear in the multi-value, if there is a multi-value, after the group by also add this field
Select Deptid,empname from Employee GROUP by Deptid,empname
--where words should be filtered using the having AVG (salary) before the GROUP BY clause executes group by;
---in represents a range, equal to any one of the list
--< All (select ...)
--< any (select ...)

Select Deptid,salary from Employee GROUP by DeptID, Salary has salary> (select DeptID, AVG (Salary) from employee GR OUP by DeptID)
Select Deptid,empname,salary from Employee GROUP by Deptid,empname,salary
GROUP BY
--2--Employee information to inquire about salary greater than the average wage in their department
Select Empname,salary from employee OE where salary > (select AVG (Salary)
From employee IE where Ie.deptid=oe.deptid)


--3--Query the maximum wage for each department (the department number and the department's maximum wage).
-These departments need to meet this condition, the average wage is greater than the department number xx (self-determined) department of the average salary
Select DeptID, Max (salary) from the employee GROUP by DeptID have avg (Salary) > (select AVG (Salary) from employee where DEP tid=111)
--4--Query the highest-paid employee information (including name, hire date, salary) of employees who entered the company on the same day (same date of employment)
SELECT * FROM employee OE where salary= (select Max (Salary)
From the employee IE GROUP BY HireDate have Oe.hiredate=ie.hiredate)


--Product Category table
CREATE TABLE Shangpingzhonglei (
ID number primary Key,
Spzlname VARCHAR2 (15)
)
--Commodity table
CREATE TABLE Shangping (
SPID number primary key,
Spcid number,
Spname VARCHAR2 (15),
Spprice number (5,2),
Spnum number,
Spallsum Number (5,2)
)

--5--Query the quantity of all items under each product category, requiring the display of the category name and the corresponding quantity
Select Zl.spzlname,count (s.spcid) from
Shangping S,shangpingzhonglei ZL
where Zl.id=s.spcid GROUP by Zl.spzlname
--6--Query The total amount of all items under each product category, requiring the display of the category name and the corresponding total amount
Select Zl.spzlname,sum (s.spallsum)
From Shangping S,shangpingzhonglei ZL
where Zl.id=s.spcid GROUP by Zl.spzlname
--7--Query the data for the category with the highest total amount under all product categories, requiring the display of the category name and the corresponding total amount
--Notation 1
Select Sl.spzlname,r.sumprice from
(Select S.spcid id,sum (s.spallsum) Sumprice from shangping s GROUP by S.spcid
Having sum (s.spallsum) >=
All (select SUM (s.spallsum) from shangping s Group by S.spcid)) R
INNER JOIN Shangpingzhonglei SL on R.id=sl.id
--Notation 2

--Notation 3
Select Sl.spzlname,sum (s.spallsum) from shangping s
INNER JOIN Shangpingzhonglei SL on S.spcid=sl.id
GROUP BY S.spcid,sl.spzlname
Having sum (s.spallsum) >=
All (select SUM (s.spallsum) from shangping s GROUP by S.spcid)

SELECT * FROM Shangpingzhonglei
--1--query without any employee's department information
SELECT * FROM department where Departid not in (
Select DeptID from employee where DeptID was not null)

---inner joins: From Employye e Inner joins department d on E.deptid=d.departid;
Select E.empname,d.departid,d.departname from Employee E
INNER JOIN Department D on E.deptid=d.departid;
---outer joins:
--left OUTER join, [outer] join: On: ), (Oracle-specific syntax: ...). where E.deptid=d.departid (+);)
Select E.empname,d.departid,d.departname from Employee E
Left Outer joins Department D on E.deptid=d.departid;
--Right outer join [outer] join: On: ), (Oracle-specific syntax: ...). where E.deptid (+) =d.departid;)
Select E.empname,d.departid,d.departname from Employee E
Right outer joins Department D on E.deptid=d.departid;

--Full outer join (full outer join) On: ）
Select E.empname,d.departid,d.departname from Employee E
Full outer joins Department D on E.deptid=d.departid;

----Cross Join (Cartesian product) (no association condition) (number of records, product of left and right)
Select E.empname, D.departid, d.departname from employee E
Cross Join Department D;

--Add FOREIGN KEY constraint
ALTER TABLE employee ADD Constraint Fk_emp_departid foreign key (DeptID)
References Department (Departid)

--On Delete CASCADE (parent delete, child forced delete) on Delete set null (parent Delete, subkey set to NULL)
ALTER TABLE employee ADD Constraint Fk_emp_departid foreign key (DeptID)
References Department (Departid)
On DELETE CASCADE
--Add UNIQUE constraint
ALTER TABLE employee ADD constraint emp_empname unique (empname)
--Add NOT NULL constraint
ALTER TABLE employee Modify salary number not NULL;
--Query data dictionary view (query constraint)
Select Constraint_name,constraint_type,search_condition from user_constraints where table_name= ' EMPLOYEE '

--Invalid constraint
---activation constraints
---Create a view
Create or Replace view Emp_view
As
Select E.empname,d.departname from Employee E
INNER JOIN Department D
On E.deptid=d.departid;
--
SELECT * from Emp_view;
--Delete View
Drop View Emp_view;
The use of--exists
Select Empname,salary from Employee outer where exists (select Empid from Employee where salary>500 and Outer.empid=em PID);

Getting Started with SQL