sql基礎教程亞馬遜

SQL is used in a wide variety of programming jobs. It's important to be familiar with SQL if you are going to be interviewing soon for a software position. This is especially true if you are going to interview at a top tech company such as Amazon, Apple, or Google.

SQL被廣泛用于各種編程工作中。 如果您即將面試軟件職位，那么熟悉SQL非常重要。 如果您要去亞馬遜，蘋果或谷歌這樣的頂級科技公司面試，尤其如此。

This guide will cover basic SQL syntax as a refresher and then list some common SQL interview questions. The answers for all questions are given and you can use this information to study for your programming interview.

本指南將復習基本SQL語法，然后列出一些常見SQL面試問題。 給出了所有問題的答案，您可以使用此信息來學習編程面試。

基本SQL語法示例 (Basic SQL Syntax Example)

SQL is an international standard (ISO), but you will find some differences between implementations. This guide uses MySQL as an example because it's the most popular implementation of SQL.

SQL是國際標準(ISO)，但是您會發現實現之間存在一些差異。 本指南以MySQL為例，因為它是最流行SQL實現。

如何使用特定的數據庫 (How to use a specific database)

Here is the SQL command used to select the database containing the tables for your SQL statements:

這是用于選擇包含SQL語句表的數據庫SQL命令：

USE fcc_sql_guides_database;

SELECT和FROM子句 (SELECT and FROM clauses)

Use SELECT to determine which columns of the data you want to show in the results. There are also options you can use to show data that is not a table column.

使用SELECT確定要在結果中顯示數據的哪些列。 還有一些選項可用于顯示不是表列的數據。

The following example shows two columns selected from the “student” table, and two calculated columns. The first of the calculated columns is a meaningless number, and the other is the system date.

以下示例顯示了從“學生”表中選擇的兩列，以及兩個計算出的列。 計算的第一列是無意義的數字，另一個是系統日期。

SELECT studentID, FullName, 3+2 AS five, now() AS currentDate FROM student;

WHERE子句 (WHERE clause)

The WHERE clause specifies a condition while getting data. The WHERE clause is used to limit the number of rows returned. It's often used in a SELECT statement but can also be used in other statements such as UPDATE and DELETE.

WHERE子句指定獲取數據時的條件。 WHERE子句用于限制返回的行數。 它通常用在SELECT語句中，但也可以用在其他語句中，例如UPDATE和DELETE。

Here is the basic syntax of the WHERE clause:

這是WHERE子句的基本語法：

SELECT column1, column2
FROM table_name
WHERE [condition]

The condition in a WHERE clause can include logical operators like >, <, =, LIKE, NOT, AND, OR.

WHERE子句中的條件可以包括>，<，=，LIKE，NOT，AND或OR之類的邏輯運算符。

Here is an example of a SQL statment using the WHERE clause. It specifies that if any of the students have certain SAT scores (1000, 1400), they will not be presented:

這是使用WHERE子句SQL語句的示例。 它指定如果任何學生的SAT分數達到一定(1000、1400)，則不會顯示以下內容：

SELECT studentID, FullName, sat_score, recordUpdated
FROM student
WHERE (studentID BETWEEN 1 AND 5OR studentID = 8OR FullName LIKE '%Maximo%')AND sat_score NOT IN (1000, 1400);

訂單依據(ASC，DESC) (ORDER BY (ASC, DESC))

ORDER BY gives us a way to sort the result set by one or more of the items in the SELECT section.

ORDER BY提供了一種方法，可以按SELECT部分??中的一個或多個項目對結果集進行排序。

Here is the same list as above, but sorted by the student's Full Name. The default sort order is ascending (ASC), but to sort in the opposite order (descending) you use DESC, as in the example below:

這是與上述相同的列表，但按學生的全名排序。 默認的排序順序是升序(ASC)，但是要使用相反的順序(降序)，請使用DESC，如下例所示：

SELECT studentID, FullName, sat_score
FROM student
WHERE (studentID BETWEEN 1 AND 5OR studentID = 8OR FullName LIKE '%Maximo%')AND sat_score NOT IN (1000, 1400)
ORDER BY FullName DESC;

分組并擁有 (GROUP BY and HAVING)

GROUP BY gives us a way to combine rows and aggregate data. The HAVING clause is like the above WHERE clause, except that it acts on the grouped data.

GROUP BY為我們提供了一種合并行和匯總數據的方法。 HAVING子句類似于上面的WHERE子句，不同之處在于它作用于分組的數據。

The SQL statement below answers the question: “Which candidates received the largest number of contributions (ordered by count (*)) in 2016, but only those who had more than 80 contributions?”

下面SQL語句回答了以下問題：“ 2016年，哪些候選人獲得的捐款數量最多(按計數(*)排序)，但只有那些捐款超過80的候選人？”

Ordering this data set in a descending (DESC) order places the candidates with the largest number of contributions at the top of the list.

按降序(DESC)排序此數據集，將貢獻最大的候選者放在列表的頂部。

SELECT Candidate, Election_year, SUM(Total_$), COUNT(*)
FROM combined_party_data
WHERE Election_year = 2016
GROUP BY Candidate, Election_year
HAVING count(*) > 80
ORDER BY count(*) DESC;

常見SQL面試問題 (Common SQL Interview Questions)

什么是SQL中的內部聯接？ (What is an inner join in SQL?)

This is the default type of join if no join is specified. It returns all rows in which there is at least one match in both tables.

如果未指定連接，則這是默認的連接類型。 它返回兩個表中至少有一個匹配項的所有行。

SELECT * FROM A x JOIN B y ON y.aId = x.Id

什么是SQL中的左聯接？ (What is a left join in SQL?)

A left join returns all rows from the left table, and the matched rows from the right table. Rows in the left table will be returned even if there was no match in the right table. The rows from the left table with no match in the right table will have null for right table values.

左聯接返回左表中的所有行，并返回右表中的匹配行。 即使右表中沒有匹配項，也將返回左表中的行。 左表中沒有匹配項的行在右表中將為null對于右表值。

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

什么是SQL中的正確聯接？ (What is a right join in SQL?)

A right join returns all rows from the right table, and the matched rows from the left table. Opposite of a left join, this will return all rows from the right table even where there is no match in the left table. Rows in the right table that have no match in the left table will have null values for left table columns.

右聯接返回右表中的所有行，以及左表中的匹配行。 與左聯接相反，這將返回右表中的所有行，即使左表中沒有匹配項也是如此。 右表中與左表不匹配的行的左表列將具有null值。

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id

什么是SQL中的完全聯接或完全外部聯接？ (What is a full join or full outer join in SQL?)

A full outer join and a full join are the same thing. The full outer join or full join returns all rows from both tables, matching up the rows wherever a match can be made and placing NULLs in the places where no matching row exists.

完全外部聯接和完全聯接是同一回事。 完全外部聯接或完全聯接返回兩個表中的所有行，在可以進行匹配的地方匹配這些行，并將NULL放置在不存在匹配行的位置。

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName

以下命令的結果是什么？ (What is the result of the following command?)

DROP VIEW view_name

This will result in an error because you can’t perform a DML operation on a view. A DML operation is any operation that manipulates the data such as DROP, INSERT, UPDATE, and DELETE.

這將導致錯誤，因為您無法在視圖上執行DML操作。 DML操作是任何操作數據的操作，例如DROP，INSERT，UPDATE和DELETE。

使用ALTER命令后可以執行回滾嗎？ (Can we perform a rollback after using ALTER command?)

No, because ALTER is a DDL command and Oracle server performs an automatic COMMIT when the DDL statements are executed. DDL statements define data structures such as CREATE table and ALTER table.

不可以，因為ALTER是DDL命令，并且在執行DDL語句時Oracle服務器執行自動COMMIT。 DDL語句定義數據結構，例如CREATE table和ALTER table 。

在列級別執行規則的唯一約束是什么？ (Which is the only constraint that enforces rules at column level?)

NOT NULL is the only constraint that works at the column level.

NOT NULL是在列級別上起作用的唯一約束。

SQL中的偽列是什么？ 舉一些例子？ (What are the pseudocolumns in SQL? Give some examples?)

A pseudocolumn behaves like a column, but is not actually stored in the table because it is all generated values. The values of a pseudocolumn can be selected but they cannot be inserted, updated, or deleted.

偽列的行為類似于列，但實際上并沒有存儲在表中，因為它都是生成的值。 可以選擇偽列的值，但是不能插入，更新或刪除它們。

ROWNUM, ROWID, USER, CURRVAL, NEXTVAL etc.

創建一個密碼為“ kmd26pt”的用戶“ my723acct”。 使用PO8提供的“ user_data”和臨時數據表空間，并向該用戶提供“ user_data”中10M的存儲空間和“ temporary_data”中5M的存儲空間。 (Create a user "my723acct" with password "kmd26pt". Use the "user_data" and temporary data tablespaces provided by PO8 and provide to this user 10M of storage space in "user_data" and 5M of storage space in "temporary_data".)

CREATE USER my723acct IDENTIFIED BY kmd26pt
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temporary_data
QUOTA 10M on user_data QUOTA 5M on temporary_data

創建角色 role_tables_and_views 。 (Create the role role_tables_and_views.)

CREATE ROLE role_tables_and_views

向上一個問題的角色授予連接數據庫的特權以及創建表和視圖的特權。 (Grant to the role of the previous question the privileges to connect to the database and the privileges to create tables and views.)

The privilege to connect to the database is CREATE SESSION The privilege to create table is CREATE TABLE The privilege to create view is CREATE VIEW

連接數據庫的特權是CREATE SESSION創建表的特權是CREATE TABLE創建視圖的特權是CREATE VIEW

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO role_tables_and_views

將問題的先前角色授予用戶anny和rita 。 (Grant the previous role in the question to the users anny and rita.)

GRANT role_tables_and_views TO anny, rita

編寫命令以將用戶rita的密碼從“ abcd”更改為“ dfgh” (Write a command to change the password of the user rita from "abcd" to "dfgh")

ALTER USER rita IDENTIFIED BY dfgh

用戶rita和anny在scott創建的INVENTORY表上沒有SELECT特權。 編寫命令以允許scott向用戶授予這些表的SELECT特權。 (The users rita and anny do not have SELECT privileges on the table INVENTORY that was created by scott. Write a command to allow scott to grant the users SELECT privileges on theses ?tables.)

GRANT select ON inventory TO rita, anny

用戶 rita 已轉移，不再需要通過角色授予她的特權 role_tables_and_views 。 編寫命令以將其從先前授予的特權中刪除。 她仍然應該能夠連接到數據庫。 (User rita has been transferred and no longer needs the privilege that was granted to her through the role role_tables_and_views. Write a command to remove her from her previously given privileges. She should still be able to connect to the database.)

REVOKE select ON scott.inventory FROM rita
REVOKE create table, create view FROM rita

已轉移的用戶rita現在移至另一家公司。 由于她創建的對象不再使用，因此編寫命令以刪除該用戶及其所有對象。 (The user rita who was transferred is now moving to another company. Since the objects she created are no longer used, write a command to remove this user and all her objects.)

The CASCADE option is necessary to remove all the objects of the user in the database.

CASCADE選項對于刪除數據庫中用戶的所有對象是必需的。

DROP USER rita CASCADE

編寫SQL查詢以從“雇員”表中找到第n個最高的“工資”。 (Write an SQL query to find the nth highest "Salary" from the "Employee" table.)

SELECT TOP 1 SalaryFROM (SELECT DISTINCT TOP N SalaryFROM EmployeeORDER BY Salary DESC)ORDER BY Salary ASC

結論 (Conclusion)

If you think you can answer all these questions, you may be ready for your interview. Good luck!

如果您認為自己可以回答所有這些問題，則可能已準備好接受面試。 祝好運！

翻譯自: https://www.freecodecamp.org/news/common-sql-interview-questions/

sql基礎教程亞馬遜