数据库：基础SQL知识+SQL实验2

（1）基础知识：

1.JOIN（连接）：

连接操作用于根据指定的条件将两个或多个表中的数据行合并在一起。JOIN 可以根据不同的条件和方式执行，包括等值连接、不等值连接等。

（1）EquiJoin（等值连接）：

等值连接是 JOIN 的一种，它基于两个表中的列具有相等值的行进行连接。等值连接使用 = 来比较列的值。特点：连接条件是等值条件，即两个表中的特定列的值必须相等。

SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Inner Join（内连接） 是一个通用的 JOIN 操作，它可以基于任意连接条件返回两个表中的匹配行。

Equi Join（等值连接） 是内连接的一种特殊形式，它限定连接条件为两个表中的列具有相等值。

SELECT * FROM   Emp E INNER JOIN dept D ON e.Deptno = d.Deptno

Similar to： SELECT *  FROM   emp E, dept D WHERE E.Deptno = d.Deptno;

SELECT Emp.Ename, emp.Deptno, DName FROM Employee INNER JOIN Department      USING(Deptno);

（1.1）Natural Join（自然连接）：

自然连接是一种特殊的等值连接，它基于两个表中具有相同列名且相等值的列进行连接。自然连接省略了 ON 子句中的条件。特点：不需要指定连接条件，它自动匹配具有相同列名的列。

SELECT * FROM table1 NATURAL JOIN table2;

（1.2）Left Outer Join（左外连接）：

左外连接返回左表中的所有行，以及右表中与左表中的匹配行。如果没有匹配，右表中的列将包含 NULL。特点：返回左表的所有行，与右表匹配的行包含在结果中，没有匹配的部分用 NULL 填充。

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;

（1.3）Right Outer Join（右外连接）：

右外连接返回右表中的所有行，以及左表中与右表中的匹配行。如果没有匹配，左表中的列将包含 NULL。特点：返回右表的所有行，与左表匹配的行包含在结果中，没有匹配的部分用 NULL 填充。

SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;

（1.4）Full Outer Join（全外连接）：

全外连接返回左表和右表中的所有行，如果没有匹配，将使用 NULL 值填充。特点：返回左表和右表的所有行，没有匹配的部分用 NULL 填充。

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

（2）Theta Join（θ连接）：

θ连接是一种使用任意条件连接两个表的一般连接。连接条件可以是任何比较操作，而不仅仅是等值条件。特点：使用任意条件进行连接，条件不限于等值条件。例如：

SELECT * FROM table1, table2 WHERE table1.column_name < table2.column_name;

（3）Cross Join（交叉连接）：

是一种 JOIN 操作，它返回两个表的笛卡尔积，即两个表中的每一行与另一个表中的每一行都进行组合。Cross Join 会返回一个新表，其中的行数等于两个表的行数的乘积。

SELECT * FROM table1 CROSS JOIN table2;（Similary to Select * from table1,table2）

2.UNION（结合）：

（1）SELECT * FROM table1 UNION SELECT * FROM table2 能够去除重复行。

（2）SELECT * FROM table1 UNION  ALL SELECT * FROM table2 能够保留所有行。

3.FUNCTIONS（部分函数）：

• LOWER： 将字符串转换为小写。

  SELECT LOWER('Hello World') AS LowercaseString; -- 结果：hello world

• UPPER： 将字符串转换为大写。

  SELECT UPPER('Hello World') AS UppercaseString; -- 结果：HELLO WORLD

• INITCAP： 将字符串每个单词的首字母转换为大写，其余字母转换为小写。

  SELECT INITCAP('hello world') AS CapitalizedString; -- 结果：Hello World

• SUBSTR： 提取字符串的子串。

  SELECT SUBSTR('Hello World', 1, 5) AS Substring; -- 结果：Hello

• LENGTH： 返回字符串的长度。

  SELECT LENGTH('Hello World') AS StringLength; -- 结果：11

• LPAD： 在字符串左侧填充指定字符。

  SELECT LPAD('123', 5, '0') AS PaddedString; -- 结果：00123

• RPAD： 在字符串右侧填充指定字符。

  SELECT RPAD('123', 5, '0') AS PaddedString; -- 结果：12300

• LTRIM： 去除字符串左侧的空格。

  SELECT LTRIM(' Hello') AS TrimmedString; -- 结果：Hello

• RTRIM： 去除字符串右侧的空格。

  SELECT RTRIM('Hello ') AS TrimmedString; -- 结果：Hello

• REPLACE： 替换字符串中的子串。

  SELECT REPLACE('Hello World', 'Hello', 'Hi') AS ReplacedString; -- 结果：Hi World

• CONCAT： 连接两个字符串。

  SELECT CONCAT('Hello', ' World') AS ConcatenatedString; -- 结果：Hello World

• ROUND： 对数值进行四舍五入。

  SELECT ROUND(3.14159, 2) AS RoundedNumber; -- 结果：3.14

• TRUNC： 截断数值为指定小数位数。

  SELECT TRUNC(3.14159, 2) AS TruncatedNumber; -- 结果：3.14

• MONTHS_BETWEEN： 计算两个日期之间的月数差。

  SELECT MONTHS_BETWEEN(TO_DATE('2022-01-01', 'YYYY-MM-DD'), TO_DATE('2022-03-01', 'YYYY-MM-DD')) AS MonthDifference; -- 结果：-2

• ADD_MONTHS： 在日期上添加指定的月数。

  SELECT ADD_MONTHS(TO_DATE('2022-01-01', 'YYYY-MM-DD'), 2) AS NewDate; -- 结果：2022-03-01

• NEXT_DAY： 找到指定日期之后的第一个特定工作日。

  SELECT NEXT_DAY(TO_DATE('2022-01-01', 'YYYY-MM-DD'), 'MONDAY') AS NextMonday; -- 结果：2022-01-03

• TO_DATE： 将字符串转换为日期。

  SELECT TO_DATE('2022-01-01', 'YYYY-MM-DD') AS ConvertedDate; -- 结果：2022-01-01

• TO_CHAR： 将日期转换为字符串。

  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS FormattedDate; -- 结果：当前日期和时间的格式化字符串

• NVL： 如果第一个表达式为 NULL，则返回第二个表达式；否则返回第一个表达式。

  SELECT NVL(NULL, 'Default') AS Result; -- 结果：Default

• NVL2： 如果第一个表达式不为 NULL，则返回第二个表达式；否则返回第三个表达式。

  SELECT NVL2('Value', 'Not Null', 'Null') AS Result; -- 结果：Not Null

• DECODE： 类似于 CASE 语句，根据条件返回不同的值。

  SELECT DECODE(1, 1, 'One', 2, 'Two', 'Other') AS Result; -- 结果：One

• SOUNDEX： 返回字符串的 SOUNDEX 值，用于模糊匹配发音相似的字符串。

  SELECT SOUNDEX('hello') AS SoundexValue; -- 结果：H400

• DATE.FORMAT： 将日期格式化为指定的格式。

  SELECT DATE_FORMAT(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS FormattedDate; -- 结果：当前日期和时间的格式化字符串

• COUNT： 计算行数或满足条件的行数。

  SELECT COUNT(*) AS RowCount FROM table_name; -- 结果：表中的行数

• AVG： 计算数值列的平均值。

  SELECT AVG(column_name) AS AverageValue FROM table_name; -- 结果：数值列的平均值

• MAX： 返回数值列的最大值。

  SELECT MAX(column_name) AS MaxValue FROM table_name; -- 结果：数值列的最大值

• MIN： 返回数值列的最小值。

  SELECT MIN(column_name) AS MinValue FROM table_name; -- 结果：数值列的最小值

• SUM： 计算数值列的总和。

  SELECT SUM(column_name) AS TotalSum FROM table_name; -- 结果：数值列的总和

（2）实验内容：

【1】JOIN：

1. Find the name and salary of employees in Luton.

2. Join the DEPT table to the EMP table and show in department number order.

3. List the names of all salesmen who work in SALES

4. List all departments that do not have any employees.

5.For each employee whose salary exceeds his manager's salary, list the employee's name and salary and the manager's name and salary.

6.List the employees who have BLAKE as their manager.

7. List all the employee Name and his Manager’s name, even if that employee doesn’t have a manager

【2】FUNCTIONS：

1 Find how many employees have a title of manager without listing them.

2 Compute the average annual salary plus commission for all salesmen

3 Find the highest and lowest salaries and the difference between them (single SELECT statement)

4 Find the number of characters in the longest department name

5 Count the number of people in department 30 who receive a salary and the number of people who receive a commission (single statement).

6 List the average commission of employees who receive a commission, and the average commission of all employees (assume employees who do not receive a commission attract zero commission)

7 List the average salary of employees that receive a salary, the average commission of employees that receive a commission, the average salary plus Commission of only those employees that receive a commission and the average salary plus commission of all employees including  those that do not receive a commission. (single statement)

8 Compute the daily and hourly salary for employees in department 30, round to the nearest penny. Assume there are 22 working days in a month and 8 working hours in a day.

9.Issue the same query as the previous one except that this time truncate (TRUNC) to the nearest penny rather than round.

【3】DATES

1.Select the name, job, and date of hire of the employees in department 20.  (Format the hiredate column using a picture MM/DD/YY)    

2.Use a picture to format hiredate as  DAY(day of the week), MONTH (name of the month, ) DD (day of the month) and YYYY(year)

3.Which employees were hired in March?

4.Which employees were hired on a Tuesday?

5.Are there any employees who have worked more than 16 years for the company?

6.Show the weekday of the first day of the month in which each employee was hired. (plus their names)

7.Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names) 

【4】GROUP BY & HAVING

1 List the department number and average salary of each department.

2.Divide all employees into groups by department and by job within department. Count the employees in each group and compute each group's average annual salary.

3.Issue the same query as above except list the department name rather than the department number.

4 List the average annual salary for all job groups having more than 2 employees in the group.

5 Find all departments with an average commission greater than 25% of average salary.

6.Find each department's average annual salary for all its employees except the managers and the president.

7. List the Department ID and Name where there are at least one Manager and two clerk and whose average salary is greater that the company’s average salary.

8. List the ID, Name, number of employee managed by the Manager who manages most employee.

9. List the name of all the Manager who manages atleast 2 employees

【5】SUB QUERIES

1 List the name and job of employees who have the same job as Jones.

2 Find all the employees in Department 10 that have a job that is the same as anyone in department 30.

3 List the name, job, and department of employees who have the same job as Jones or a salary greater than or equal to Ford.

4 Find all employees in department 10 that have a job that is the same as anyone in the Sales department

5 Find the employees located in Liverpool who have the same job as Allen. Return the results in alphabetical order by employee name.

6 Find all the employees that earn more than the average salary of employees in their department.

7 Find all the employees that earn more than JONES, using temporary labels to abbreviate table names.

【6】DATA MANIPULATION

1 Create a new table called loans with columns named LNO NUMBER (3), EMPNO NUMBER (4), TYPE CHAR(1), AMNT NUMBER (8,2)

2 Insert the following data

3 Check that you have created 3 new records in Loans

4 The Loans table must be altered to include another column OUTST NUMBER(8,2)

5 Add 10% interest to all M type loans

6 Remove all loans less than  £3000.00

7 Change the name of loans table to accounts

8 Change the name of column LNO to LOANNO

9 Create a view for use by personnel in department 30 showing employee name, number, job and hiredate

10 Use the view to show employees in department 30 having jobs which are not salesman

11 Create a view which shows summary information of total salary and no of employees for each department.