晚霞再美不及你-博客blog
幻灯片-博客blog
幻灯片-博客blog
开启精彩搜索

热门搜索

数据库
图标卡片
这是一个图标卡片示例
原创作品
这是一个图标卡片示例
灵感来源NEW
这是一个图标卡片示例
系统工具 GO
这是一个图标卡片示例
用户封面
1396170
晚霞再美不及你的头像-博客blog
钻石会员晚霞再美不及你等级-LV8-博客blog
官方管理员
这家伙很懒，什么都没有写...

1个月前
https://www.doubao.com/thread/ae3a193e0dd9f
评论于：12364都觉得就地解决
2个月前
CREATE TABLE regions ( region_id INT, region_name VARCHAR(255) ); CREATE TABLE countries ( country_id CHAR(2), country_name VARCHAR(255), region_id INT ); CREATE TABLE locations ( location_id INT, street_address VARCHAR(255), postal_code VARCHAR(255), city VARCHAR(255), state_province VARCHAR(255), country_id CHAR(2) ); CREATE TABLE departments ( department_id INT, department_name VARCHAR(255), manager_id INT, location_id INT ); CREATE TABLE employees ( employee_id INT, first_name VARCHAR(255), last_name VARCHAR(255), email VARCHAR(255), phone_number VARCHAR(255), hire_date DATE, job_id VARCHAR(255), salary DECIMAL(10, 2), commission_pct DECIMAL(3, 2), manager_id INT, department_id INT ); CREATE TABLE job_history ( employee_id INT, start_date DATE, end_date DATE, job_id VARCHAR(255), department_id INT ); CREATE TABLE jobs ( job_id VARCHAR(255), job_title VARCHAR(255), min_salary DECIMAL(10, 2), max_salary DECIMAL(10, 2) ); CREATE TABLE job_grades ( grade VARCHAR(255), lowest_sal DECIMAL(10, 2), highest_sal DECIMAL(10, 2) ); INSERT INTO `countries` (`COUNTRY_ID`, `COUNTRY_NAME`, `REGION_ID`) VALUES ('AR', 'Argentina', '2'), ('AU', 'Australia', '3'), ('BE', 'Belgium', '1'), ('BR', 'Brazil', '2'), ('CA', 'Canada', '2'), ('CH', 'Switzerland', '1'), ('CN', 'China', '3'), ('DE', 'Germany', '1'), ('DK', 'Denmark', '1'), ('EG', 'Egypt', '4'), ('FR', 'France', '1'), ('HK', 'HongKong', '3'), ('IL', 'Israel', '4'), ('IN', 'India', '3'), ('IT', 'Italy', '1'), ('JP', 'Japan', '3'), ('KW', 'Kuwait', '4'), ('MX', 'Mexico', '2'), ('NG', 'Nigeria', '4'), ('NL', 'Netherlands', '1'), ('SG', 'Singapore', '3'), ('UK', 'United Kingdom', '1'), ('US', 'United States of America', '2'), ('ZM', 'Zambia', '4'), ('ZW', 'Zimbabwe', '4'); INSERT INTO `departments` (`DEPARTMENT_ID`, `DEPARTMENT_NAME`, `MANAGER_ID`, `LOCATION_ID`) VALUES ('10', 'Administration', '200', '1700'), ('20', 'Marketing', '201', '1800'), ('30', 'Purchasing', '114', '1700'), ('40', 'Human Resources', '203', '2400'), ('50', 'Shipping', '121', '1500'), ('60', 'IT', '103', '1400'), ('70', 'Public Relations', '204', '2700'), ('80', 'Sales', '145', '2500'), ('90', 'Executive', '100', '1700'), ('100', 'Finance', '108', '1700'), ('110', 'Accounting', '205', '1700'), ('120', 'Treasury', '0', '1700'), ('130', 'Corporate Tax', '0', '1700'), ('140', 'Control And Credit', '0', '1700'), ('150', 'Shareholder Services', '0', '1700'), ('160', 'Benefits', '0', '1700'), ('170', 'Manufacturing', '0', '1700'), ('180', 'Construction', '0', '1700'), ('190', 'Contracting', '0', '1700'), ('200', 'Operations', '0', '1700'), ('210', 'IT Support', '0', '1700'), ('220', 'NOC', '0', '1700'), ('230', 'IT Helpdesk', '0', '1700'), ('240', 'Government Sales', '0', '1700'), ('250', 'Retail Sales', '0', '1700'), ('260', 'Recruiting', '0', '1700'), ('270', 'Payroll', '0', '1700'); INSERT INTO `employees` (`EMPLOYEE_ID`, `FIRST_NAME`, `LAST_NAME`, `EMAIL`, `PHONE_NUMBER`, `HIRE_DATE`, `JOB_ID`, `SALARY`, `COMMISSION_PCT`, `MANAGER_ID`, `DEPARTMENT_ID`) VALUES ('100', 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17', 'AD_PRES', '24000.00', '0.00', '0', '90'), ('101', 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1987-06-18', 'AD_VP', '17000.00', '0.00', '100', '90'), ('102', 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '1987-06-19', 'AD_VP', '17000.00', '0.00', '100', '90'), ('103', 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1987-06-20', 'IT_PROG', '9000.00', '0.00', '102', '60'), ('104', 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1987-06-21', 'IT_PROG', '6000.00', '0.00', '103', '60'), ('105', 'David', 'Austin', 'DAUSTIN', '590.423.4569', '1987-06-22', 'IT_PROG', '4800.00', '0.00', '103', '60'), ('106', 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '1987-06-23', 'IT_PROG', '4800.00', '0.00', '103', '60'), ('107', 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '1987-06-24', 'IT_PROG', '4200.00', '0.00', '103', '60'), ('108', 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '1987-06-25', 'FI_MGR', '12000.00', '0.00', '101', '100'), ('109', 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '1987-06-26', 'FI_ACCOUNT', '9000.00', '0.00', '108', '100'); INSERT INTO `job_history` (`EMPLOYEE_ID`, `START_DATE`, `END_DATE`, `JOB_ID`, `DEPARTMENT_ID`) VALUES ('102', '1993-01-13', '1998-07-24', 'IT_PROG', '60'), ('101', '1989-09-21', '1993-10-27', 'AC_ACCOUNT', '110'), ('101', '1993-10-28', '1997-03-15', 'AC_MGR', '110'), ('201', '1996-02-17', '1999-12-19', 'MK_REP', '20'), ('114', '1998-03-24', '1999-12-31', 'ST_CLERK', '50'), ('122', '1999-01-01', '1999-12-31', 'ST_CLERK', '50'), ('200', '1987-09-17', '1993-06-17', 'AD_ASST', '90'), ('176', '1998-03-24', '1998-12-31', 'SA_REP', '80'), ('176', '1999-01-01', '1999-12-31', 'SA_MAN', '80'), ('200', '1994-07-01', '1998-12-31', 'AC_ACCOUNT', '90'); INSERT INTO `jobs` (`JOB_ID`, `JOB_TITLE`, `MIN_SALARY`, `MAX_SALARY`) VALUES ('AD_PRES', 'President', '20000', '40000'), ('AD_VP', 'Administration Vice President', '15000', '30000'), ('AD_ASST', 'Administration Assistant', '3000', '6000'), ('FI_MGR', 'Finance Manager', '8200', '16000'), ('FI_ACCOUNT', 'Accountant', '4200', '9000'), ('AC_MGR', 'Accounting Manager', '8200', '16000'), ('AC_ACCOUNT', 'Public Accountant', '4200', '9000'), ('SA_MAN', 'Sales Manager', '10000', '20000'), ('SA_REP', 'Sales Representative', '6000', '12000'), ('PU_MAN', 'Purchasing Manager', '8000', '15000'), ('PU_CLERK', 'Purchasing Clerk', '2500', '5500'), ('ST_MAN', 'Stock Manager', '5500', '8500'), ('ST_CLERK', 'Stock Clerk', '2000', '5000'), ('SH_CLERK', 'Shipping Clerk', '2500', '5500'), ('IT_PROG', 'Programmer', '4000', '10000'), ('MK_MAN', 'Marketing Manager', '9000', '15000'), ('MK_REP', 'Marketing Representative', '4000', '9000'), ('HR_REP', 'Human Resources Representative', '4000', '9000'), ('PR_REP', 'Public Relations Representative', '4500', '10500'); INSERT INTO `locations` (`LOCATION_ID`, `STREET_ADDRESS`, `POSTAL_CODE`, `CITY`, `STATE_PROVINCE`, `COUNTRY_ID`) VALUES ('1000', '1297 Via Cola di Rie', '989', 'Roma', '', 'IT'), ('1100', '93091 Calle della Testa', '10934', 'Venice', '', 'IT'), ('1200', '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP'), ('1300', '9450 Kamiya-cho', '6823', 'Hiroshima', '', 'JP'), ('1400', '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US'), ('1500', '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'), ('1600', '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US'), ('1700', '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'), ('1800', '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA'), ('1900', '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA'), ('2000', '40-5-12 Laogianggen', '190518', 'Beijing', '', 'CN'), ('2100', '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN'), ('2200', '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU'), ('2300', '198 Clementi North', '540198', 'Singapore', '', 'SG'), ('2400', '8204 Arthur St', '', 'London', '', 'UK'), ('2500', '"Magdalen Centre', ' The Oxford ', 'OX9 9ZB', 'Oxford', 'UK'), ('2600', '9702 Chester Road', '9629850293', 'Stretford', 'Manchester', 'UK'), ('2700', 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE'), ('2800', 'Rua Frei Caneca 1360', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR'), ('2900', '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH'), ('3000', 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH'), ('3100', 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL'), ('3200', 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal', 'MX'); INSERT INTO `regions` (`REGION_ID`, `REGION_NAME`) VALUES ('1', 'Europe'), ('2', 'Americas'), ('3', 'Asia'), ('4', 'Middle East and Africa'); SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees; SELECT DISTINCT department_id FROM employees; SELECT * FROM employees ORDER BY first_name DESC; SELECT first_name, last_name, salary, salary * 0.15 AS PF FROM employees; SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary; SELECT SUM(salary) AS total_salaries_payable FROM employees; SELECT MAX(salary) AS maximum_salary, MIN(salary) AS minimum_salary FROM employees; SELECT COUNT(*) AS number_of_employees FROM employees; SELECT COUNT(DISTINCT job_id) AS number_of_jobs FROM employees; SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000; SELECT first_name, last_name, department_id FROM employees WHERE department_id IN (30, 100) ORDER BY first_name, last_name; SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000 AND department_id IN (30, 100); SELECT first_name, last_name, hire_date FROM employees WHERE YEAR(hire_date) = 1987; SELECT first_name FROM employees WHERE first_name LIKE '%b%' AND first_name LIKE '%c%'; SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'IT_PROG' OR job_id = 'SH_CLERK') AND salary NOT IN (4500, 10000, 15000); SELECT last_name FROM employees WHERE LENGTH(last_name) = 6; SELECT last_name FROM employees WHERE last_name LIKE '__e%'; SELECT DISTINCT job_id FROM employees; SELECT first_name, last_name, salary, salary * 0.15 AS PF FROM employees; SELECT COUNT(DISTINCT job_id) AS number_of_jobs FROM employees; SELECT SUM(salary) AS total_salaries_payable FROM employees; SELECT MIN(salary) AS minimum_salary FROM employees; SELECT MAX(salary) AS maximum_programmer_salary FROM employees WHERE job_id = 'IT_PROG'; SELECT AVG(salary) AS average_salary_department_90, COUNT(*) AS number_of_employees_department_90 FROM employees WHERE department_id = 90; SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary, SUM(salary) AS total_salary, AVG(salary) AS average_salary FROM employees; SELECT job_id, COUNT(*) AS number_of_employees_with_same_job FROM employees GROUP BY job_id; SELECT MAX(salary) - MIN(salary) AS salary_difference FROM employees; SELECT manager_id, MIN(salary) AS lowest_paid_salary FROM employees GROUP BY manager_id; SELECT department_id, SUM(salary) AS total_salary_per_department FROM employees GROUP BY department_id; SELECT job_id, AVG(salary) AS average_salary_per_job FROM employees WHERE job_id!= 'IT_PROG' GROUP BY job_id; SELECT job_id, SUM(salary) AS total_salary_job_wise_department_90, MAX(salary) AS maximum_salary_job_wise_department_90, MIN(salary) AS minimum_salary_job_wise_department_90, AVG(salary) AS average_salary_job_wise_department_90 FROM employees WHERE department_id = 90 GROUP BY job_id; SELECT job_id, MAX(salary) AS maximum_salary FROM employees GROUP BY job_id HAVING MAX(salary) >= 4000; SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id HAVING COUNT(*) > 10; SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bull'); SELECT first_name, last_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT'); SELECT first_name, last_name FROM employees e WHERE manager_id IS NOT NULL AND department_id IN (SELECT department_id FROM departments d JOIN locations l ON d.location_id = l.location_id JOIN countries c ON l.country_id = c.country_id WHERE c.country_name = 'United States of America'); SELECT first_name, last_name FROM employees WHERE employee_id IN (SELECT manager_id FROM departments WHERE manager_id IS NOT NULL); SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT e.first_name, e.last_name, e.salary FROM employees e JOIN jobs j ON e.job_id = j.job_id JOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal AND jg.highest_sal WHERE e.salary = (SELECT MIN(salary) FROM employees WHERE job_id = e.job_id); SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) AND department_id IN (SELECT department_id FROM departments WHERE department_name LIKE '%IT%'); SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bell'); SELECT first_name, last_name, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE job_id = 'SH_CLERK') ORDER BY salary; SELECT first_name, last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM departments WHERE manager_id IS NOT NULL); SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM employees e JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) d_avg ON e.department_id = d_avg.department_id WHERE e.salary > d_avg.avg_salary; SELECT salary FROM employees e1 WHERE 4 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary); SELECT salary FROM employees e1 WHERE 3 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary) ORDER BY salary DESC LIMIT 3; SELECT salary FROM employees e1 WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary (SELECT hire_date FROM employees WHERE last_name = 'Jones'); SELECT d.department_name, COUNT(e.employee_id) AS number_of_employees FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_name; SELECT e.employee_id, j.job_title, DATEDIFF(jh.end_date, jh.start_date) AS days_between_dates FROM job_history jh JOIN employees e ON jh.employee_id = e.employee_id JOIN jobs j ON jh.job_id = j.job_id WHERE jh.department_id = 90; SELECT d.department_id, d.department_name, m.first_name FROM departments d JOIN employees m ON d.manager_id = m.employee_id; SELECT d.department_name, CONCAT(m.first_name,'', m.last_name) AS manager_name, l.city FROM departments d JOIN employees m ON d.manager_id = m.employee_id JOIN locations l ON d.location_id = l.location_id; SELECT j.job_title, AVG(e.salary) AS average_salary FROM employees e JOIN jobs j ON e.job_id = j.job_id GROUP BY j.job_title; SELECT j.job_title, CONCAT(e.first_name,'', e.last_name) AS employee_name, e.salary - j.min_salary AS salary_difference FROM employees e JOIN jobs j ON e.job_id = j.job_id; SELECT jh.employee_id, jh.start_date, jh.end_date, jh.job_id, jh.department_id FROM job_history jh JOIN employees e ON jh.employee_id = e.employee_id WHERE e.salary > 10000; SELECT d.department_name, CONCAT(m.first_name, ' ', m.last_name) AS manager_name, m.hire_date, m.salary FROM departments d JOIN employees m ON d.manager_id = m.employee_id WHERE TIMESTAMPDIFF(YEAR, m.hire_date, CURDATE()) > 15;
@晚霞再美不及你评论于：数据库考试代码
2个月前
[代码]
评论于：数据库考试代码
4个月前
figma组件库： https://www.figcomponents.com/components/landing-page
评论于：Web Technology网络技术LAB-4 CSS
4个月前
https://www.doubao.com/thread/a2afef41e887f
评论于：Web Technology网络技术LAB-4 CSS
4个月前
https://www.doubao.com/thread/af96375450a3f
评论于：Web Technology网络技术LAB-4 CSS
5个月前
死吧洗吧
评论于：数据库MySQL代码

发布文章发布帖子
在手机上浏览此页面