最高のSQLの例

SQLはStructuredQueryLanguageの略です。あらゆる種類のリレーショナルデータベースで使用されます。

基本的なSQL構文の例

このガイドでは、SQLステートメントの構文の基本的な高レベルの説明を提供します。

SQLは国際標準(ISO)ですが、実装間で多くの違いがあります。このガイドでは、例としてMySQLを使用しています。他の多くのリレーショナルデータベースマネージャー(DBMS)のいずれかを使用する場合は、必要に応じてそのDBMSのマニュアルを確認する必要があります。

カバーする内容

  • 使用(ステートメントが使用するデータベースを設定します)
  • Select句とFrom句
  • Where句(および/または、IN、Between、LIKE)
  • 注文者(ASC、DESC)
  • GroupbyとHaving

これを使用する方法

これは、SQLステートメントのテーブルを含むデータベースを選択するために使用されます。

use fcc_sql_guides_database; -- select the guide sample database 

Select句とFrom句

選択部分は通常、結果に表示するデータの列を決定するために使用されます。テーブル列ではないデータを表示するために使用できるオプションもあります。

この例は、「student」テーブルから選択された2つの列と、2つの計算された列を示しています。計算された列の最初は無意味な数値で、もう1つはシステム日付です。

 select studentID, FullName, 3+2 as five, now() as currentDate from student; 
画像-1

構文01.JPG767×241

Where句(および/または、IN、BetweenおよびLIKE)

WHERE句は、返される行数を制限するために使用されます。

この場合、これら5つすべてが使用されますが、これはややばかげたWhere句です。

この結果を上記のSQLステートメントと比較して、このロジックに従います。

次の行が表示されます。

  • 1から5までの学生IDを持っている(両端を含む)
  • またはstudentID = 8
  • または名前に「Maxmimo」が含まれている

次の例も同様ですが、生徒のいずれかが特定の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); 
画像-1

注文者(ASC、DESC)

Order Byを使用すると、SELECTセクションの1つ以上の項目で結果セットを並べ替えることができます。これは上記と同じリストですが、学生のフルネームでソートされています。デフォルトのソート順は昇順(ASC)ですが、逆の順序(降順)でソートするには、以下の例のようにDESCを使用します。

 select studentID, FullName, sat_score from student where (studentID between 1 and 5 -- inclusive or studentID = 8 or FullName like '%Maximo%') and sat_score NOT in (1000, 1400) order by FullName DESC; 
画像-1

GroupByとHaving

Group Byを使用すると、行を組み合わせてデータを集計することができます。持っている句は、グループ化されたデータに作用することを除いて、上記のWhere句と同じです。

このデータは、これらのガイドの一部で使用しているキャンペーン貢献データからのものです。

このSQLステートメントは、「2016年に最も多くの貢献($金額ではなく、カウント(*))を受け取った候補者のうち、80を超える貢献をした候補者だけですか?」という質問に答えます。

このデータセットを降順(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; 
画像-1

これらすべてのSQLと同様に、この入門ガイドにあるものよりもはるかに多くのものがあります。私はこれが少なくともあなたが始めるのに十分なものになることを願っています。データベースマネージャのマニュアルを参照して、さまざまなオプションを自分で試してみてください。

SQLインタビューの一般的な質問

SQLの内部結合とは何ですか?

これは、結合が指定されていない場合のデフォルトの結合タイプです。両方のテーブルに少なくとも1つの一致があるすべての行を返します。

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

SQLの左結合とは何ですか?

左結合は、左側のテーブルからすべての行を返し、右側のテーブルから一致した行を返します。右側のテーブルに一致するものがなかった場合でも、左側のテーブルの行が返されます。右側のテーブルに一致しない左側のテーブルの行はnull、右側のテーブルの値になります。

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

SQLの右結合とは何ですか?

右結合は、右側のテーブルからすべての行を返し、左側のテーブルから一致した行を返します。左結合の反対に、これは、左テーブルに一致がない場合でも、右テーブルからすべての行を返します。左側のテーブルに一致しない右側のテーブルの行には、null左側のテーブルの列の値があります。

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

SQLの完全結合とは何ですか?

完全結合は、いずれかのテーブルに一致するすべての行を返します。したがって、左側のテーブルに一致する行が右側のテーブルにない行がある場合、それらは含まれます。右側のテーブルに左側のテーブルに一致する行がない場合と同様に、それらが含まれます。

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

次のコマンドの結果は何ですか?

 DROP VIEW view_name 

ビューでDML操作を実行できないため、ここではエラーになります。

ALTERコマンドを使用した後にロールバックを実行できますか?

いいえ、ALTERはDDLコマンドであり、OracleサーバーはDDLステートメントの実行時に自動COMMITを実行するためです。

列レベルでルールを適用する唯一の制約はどれですか?

NOT NULLは、列レベルで機能する唯一の制約です。

SQLの疑似列とは何ですか?いくつか例を挙げてください。

疑似列は、システムで生成された値を返す関数です。疑似列は、Oracleデータベース列と同じコンテキストで使用されるがディスクには保存されないOracle割り当て値であるため、そのように知られている理由。

 ROWNUM, ROWID, USER, CURRVAL, NEXTVAL etc. 

パスワードkmd26ptでユーザーmy723acctを作成します。PO8によって提供されるユーザーデータと一時データのテーブルスペースを使用して、このユーザーにユーザーデータに10Mのストレージスペースとtemporary_dataに5Mのストレージスペースを提供します。

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

ロールロールテーブルand_viewsを作成します。

 CREATE ROLE role_tables_and_views 

前の質問の役割に、データベースに接続するための特権と、テーブルとビューを作成するための特権を付与します。

データベースに接続する権限はCREATESESSIONです。テーブルを作成する権限はCREATETABLEです。ビューを作成する権限はCREATEVIEWです。

 GRANT Create session, create table, create view TO role_tables_and_views 

質問の前の役割をユーザーのannyとritaに付与します

 GRANT role_tables_and_views TO anny, rita 

パスワードkmd26ptでユーザーmy723acctを作成します。PO8によって提供されるユーザーデータと一時データのテーブルスペースを使用して、このユーザーにユーザーデータに10Mのストレージスペースとtemporary_dataに5Mのストレージスペースを提供します。

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

ロールロールテーブルand_viewsを作成します。

 CREATE ROLE role_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.

 GRANT Create session, create table, create view TO role_tables_and_views 

Grant the previous role in the question to the users anny and rita

 GRANT role_tables_and_views TO anny, rita 

Write a command to change the password of the user rita from abcd to dfgh

 ALTER USER rita IDENTIFIED BY dfgh 

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 priviliges on these tables.

 GRANT select ON inventory TO rita, anny 

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 previous given priviliges except that she still could connect to the database.

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

The user rita who was transferred is now moving to another company. Since the objects that she created is of no longer use, write a commmand to remove this user and all her objects.

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

 DROP USER rita CASCADE ### 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 previous given priviliges except that she still could connect to the database. ``` sql REVOKE select ON scott.inventory FROM rita REVOKE create table, create view FROM rita 

The user rita who was transferred is now moving to another company. Since the objects that she created is of no longer use, write a command to remove this user and all her objects.

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

 DROP USER rita CASCADE 

Write SQL query to find the nth highest salary from table.

 SELECT TOP 1 Salary FROM ( SELECT DISTINCT TOP N Salary FROM Employee ORDER BY Salary DESC ) ORDER BY Salary ASC

SQL Create View Statement

What is a View?

A View is a database object that presents data existing in one or more tables. Views are used in a similar way to tables, but they don’t contain any data. They just “point” to the data that exists elsewhere (tables or views, for example).

Why do we like them?

  • Views are a way to limit the data presented. For example, the human resources department data filtered to only present non-sensitve information. Sensitive information in this case could be social security numbers, sex of employee, payrate, home address, etc.
  • Complex data across more than one table can be combined into a single “view.” This can make life easier for your business analysts and programmers.

Important Safety Tips

  • Views are managed by the system. When data in the related tables are changed, added, or updated, the View is updated by the system. We want to use these only when needed to manage use of system resources.
  • In MySQL, changes to the table design (that is, new or dropped columns) made AFTER a view is created are not updated in the view itself. The view would have to be updated or recreated.
  • Views are one of the four standard database object types. The others are tables, stored procedures, and functions.
  • Views can usually be treated as you would a table, but updates are limited or not available when the view contains more than one table.
  • There are many other details about views that are beyond the scope of this introductory guide. Spend time with your database managers manual and have fun with this powerful SQL object.

Syntax of the Create View Statement (MySQL)

CREATE [OR REPLACE] [ALGORITHM = UNDEFINED ] [DEFINER =  user ] [SQL SECURITY  DEFINER ] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] 

This guide will cover this part of of the statement…

CREATE VIEW view_name [(column_list)] AS select_statement 

Sample View creation from the student tables

Notes:

  • The name of the view has a “v” at the end. It’s recommended that the view name indicate that it’s a view in some way to make life easier for programmers and database administrators. Your IT shop should have its own rules on naming objects.
  • The columns in the view are limited by the SELECT and the rows of data by the WHERE clause.
  • the ”`” character around the view names is required because of the ”-” in the names. MySQL reports an error without them.
create view `programming-students-v` as select FullName, programOfStudy from student where programOfStudy = 'Programming'; select * from `programming-students-v`; 

Sample of using a View to combine data from more than one table

A Student demographics table was added to the database to demonstrate this usage. This view will combine these tables.

Notes:

  • To “join” tables, the tables must have fields in common (usually primary keys) that uniquely identity each row. In this case it’s the student ID. (More on this in the SQL Joins guide.)
  • Notice the “alias” given to each table (“s” for student and “sc” for student contact). This is a tool to shorten the table names and make it easier to identify which table is being used. It’s easier than typing long table names repeatedly. In this example, it was required because studentID is the same column name in both tables, and the system would present an “ambiguous column name error” without specifying which table to use.

Guide to the SQL Between Operator

The BETWEEN Operator is useful because of the SQL Query Optimizer. Although BETWEEN is functionally the same as: x <= element <= y, the SQL Query Optimizer will recognize this command faster, and has optimized code for running it.

This operator is used in a WHERE clause or in a GROUP BY HAVING clause.

Rows are selected that have a value greater than the minimum value and less than the maximum value.

It’s important to keep in mind that the values entered in the command are excluded from the result. We get just what is between them.

Here is the syntax for using the function in a WHERE Clause:

select field1, testField from table1 where testField between min and max 

Here is an example using the student table and the WHERE clause:

-- no WHERE clause select studentID, FullName, studentID from student; -- WHERE clause with between select studentID, FullName, studentID from student where studentID between 2 and 7; 
画像-1

Here is an example using the campaign funds table and the having clause. This will return rows where the sum of the donations for a candidate are between $3 Million and $18 Million based on the HAVING clause in the GROUP BY part of the statement. More on aggregation in that guide.

select Candidate, Office_Sought, Election_Year, format(sum(Total_$),2) from combined_party_data where Election_Year = 2016 group by Candidate, Office_Sought, Election_Year having sum(Total_$) between 3000000 and 18000000 order by sum(Total_$) desc; 
画像-1

SQL Create Table Statement Example

A table is a group of data stored in a database.

To create a table in a database you use the CREATE TABLE statement. You give a name to the table and a list of columns with its datatypes.

CREATE TABLE TABLENAME(Attribute1 Datatype, Attribute2 Datatype,........); 

Here’s an example creating a table named Person:

CREATE TABLE Person( Id int not null, Name varchar not null, DateOfBirth date not null, Gender bit not null, PRIMARY KEY( Id ) ); 

In the example above, each Person has a Name, a Date of Birth and a Gender. The Id column is the key that identifies one person in the table. You use the keyword PRIMARY KEY to configure one or more columns as a primary key.

A column can be not null or null indicating whether it is mandatory or not.

A guide to the SQL Insert Query

Insert queries are a way to insert data into a table. Let’s say we have created a table using

CREATE TABLE example_table ( name varchar(255), age int)

example_table

Name|Age --- | --- 

Now to add some data to this table , we’ll use  INSERT  in the following way:

INSERT INTO example_table (column1,column2) VALUES ("Andrew",23)

example_table

Name|Age --- | --- Andrew|23

Even the following will work, but it’s always a good practice to specify which data is going into which column.

INSERT INTO table_name VALUES ("John", 28)

example_table

Name|Age --- | --- Andrew|23 John|28

A guide to the SQL AND operator

AND is used in a WHERE clause or a GROUP BY HAVING clause to limit the rows returned from the executed statement. Use AND when it’s required to have more than one condition met.

We’ll use the student table to present examples.

Here’s the student table without a WHERE clause:

select * from student; 
画像-1

and_operator01.JPG?raw=true760×247

Now the WHERE clause is added to display only programming students:

select * from student where programOfStudy = 'Programming'; 
画像-1

and_operator02.JPG?raw=true790×179

Now the WHERE clause is updated with AND to show results for programming students that also have a SAT score greater than 800:

select * from student where programOfStudy = 'Programming' and sat_score > 800; 
画像-1

and_operator03.JPG?raw=true790×179

This is a more complex example from the campaign contributions table. This example has a GROUP BY clause with HAVING clause using an AND to restrict the returned records to candidates from 2016 with contributions between $3 Million and $18 Million in total.

select Candidate, Office_Sought, Election_Year, FORMAT(sum(Total_$),2) from combined_party_data where Office_Sought = 'PRESIDENT / VICE PRESIDENT' group by Candidate, Office_Sought, Election_Year having Election_Year = 2016 and sum(Total_$) between 3000000 and 18000000 order by sum(Total_$) desc; 
画像-1

How to use the SQL Order By Keyword

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. Here is an SQL sorting the students by FullName in descending order. The default sort order is ascending (ASC) but to sort in the opposite order (descending) you use DESC.

SELECT studentID, FullName, sat_score FROM student ORDER BY FullName DESC; 
+-----------+------------------------+-----------+ | studentID | FullName | sat_score | +-----------+------------------------+-----------+ | 2 | Teri Gutierrez | 800 | | 3 | Spencer Pautier | 1000 | | 6 | Sophie Freeman | 1200 | | 9 | Raymond F. Boyce | 2400 | | 1 | Monique Davis | 400 | | 4 | Louis Ramsey | 1200 | | 7 | Edgar Frank "Ted" Codd | 2400 | | 8 | Donald D. Chamberlin | 2400 | | 5 | Alvin Greene | 1200 | +-----------+------------------------+-----------+ 9 rows in set (0.00 sec) 

Here is the UN-ORDERED, current, full student list to compare to the above.

SELECT studentID, FullName, sat_score, rcd_updated FROM student; 
+-----------+------------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+------------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 3 | Spencer Pautier | 1000 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 7 | Edgar Frank "Ted" Codd | 2400 | 2017-08-16 15:35:33 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+------------------------+-----------+---------------------+ 9 rows in set (0.00 sec) 

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

I hope this at least gives you enough to get started.

データベースマネージャのマニュアルを参照して、さまざまなオプションを自分で試してみてください。