sql基础教程亚马逊_针对Amazon，Apple，Google的常见
SQL⾯试问题
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⾮常重要。 如果您要去亚马逊，苹果或⾕歌这样的顶级科技公司⾯试，尤其如此。mysql语句的执行顺序
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]ascii编码中8对应的编码
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 5
OR studentID = 8
OR 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 5
OR studentID = 8
OR 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 c
ontributions (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.
如果未指定连接，则这是默认的连接类型。 它返回两个表中⾄少有⼀个匹配项的所有⾏。
定义一个java数组>好的视频模板网站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 th
e 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 INVENT
ORY 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 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Employee
ORDER 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!
如果您认为⾃⼰可以回答所有这些问题，则可能已准备好接受⾯试。 祝好运！
sql基础教程亚马逊

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。