SQL Basic Syntax &sqlite

Databases

A database is a data store for storing, querying, and processing data. The database stores the data we need and opens an interface that interacts with the data. Most technology companies use databases to organize numbers. Database system includes database management software and management control, security and access control, language and database interface these content.
First, we will focus on a structured query language for the SQL language. It is used to query, update, and modify data in the database.

Sql

SQL is one of the most common database languages and is an important tool in any data professional toolbox. Although SQL is a language, it is completely different from a language such as Python or R, which is a language built specifically to interact with the database and does not have much functionality as a traditional programming language. Because SQL is a declarative language, the user needs to focus on expressing what he wants and the computer is concentrating on how to perform the calculation.

Tables, Rows, & Columns

A database is a collection of tables, a table containing rows of data, and column information. The table and the dataframe in pandas are very similar.
The data set for this article is **recent-grads.csv**2010 to 2012 of graduate information, and its properties are as follows:

• Rank -rank by median earnings.
• major_code -Major code.
• Major -Major description.
• major_category -category of Major.
• Total-all number of people with major.
• sample_size -Sample size (unweighted) of full-time.
• men-Male graduates.
• Women -Female graduates.
• sharewomen -women as share of total.
• employed -number employed.

Querying

• Writing a query language is a basic interaction with a database, such as:

...]FROM tableName;SELECT Rank,MajorFROM recent_grads;

• where semicolon; is not omitted, it indicates the end of the query statement. This allows us to write many query statements on one line, as long as we do not write; It's not over.

Sqlite

SQLite is a lightweight database that is great for exploring and learning SQL.

SELECTFROM recent_grads;‘‘‘[["Rank""Major"], [1"PETROLEUM ENGINEERING"], [2"MINING AND MINERAL ENGINEERING"], [3"METALLURGICAL ENGINEERING"], [4"NAVAL ARCHITECTURE AND MARINE ENGINEERING"], . . .‘‘‘

Specifying Column Order

• SQL can specify the order of the columns in the Select return value, and the following major is placed in front:

SELECTFROM recent_grads;‘‘‘[["Major""Rank"], ["PETROLEUM ENGINEERING"1], ["MINING AND MINERAL ENGINEERING"2], ["METALLURGICAL ENGINEERING"3], . . .‘‘‘

Practice:select

SELECTRank,major_code,major,major_category,total fromRecent_grads;" "[["Rank","Major_code","Major","Major_category","Total"], [1,2419,"Petroleum ENGINEERING","Engineering",2339], [2,2416,"MINING and MINERAL ENGINEERING","Engineering",756], [3,2415,"Metallurgical ENGINEERING","Engineering",856], [4,2417,"NAVAL ARCHITECTURE and MARINE ENGINEERING","Engineering",1258],. . ." "

Where

We use Select to return the entire column of data, if we want to return a column to meet some conditions of the data, you need to use the where statement, where the statement is used to filter.

For example, we want to get more major girls than boys, use where need three things:

• The column we want the database to filter on: sharewomen
• A comparison operator to specify what we want a value in a column to be compared: >
• The comparison value we want the database to compare each value to: 0.5

select  major,sharewomen< Span class= "Hljs-keyword" >from  recent_gradswhere  sharewomen > 0.5 ;  "  [[ "Major" ,  "Sharewomen" ], [  "actuarial science" , 0.535714286 ], [ "computer Science" , 0.578766338 ],[  "environmental ENGINEERING" , 0.558548009 ],[< Span class= "hljs-string" > "nursing" , 0.896018988 ], ... "    

The comparer that can be used in the WHERE statement:< <= > >= =! =

Practice:where

SELECTFROMWHERE10000;‘‘‘[["Major""Employed"], ["CHEMICAL ENGINEERING"25694], ["MECHANICAL ENGINEERING"76442], ["ELECTRICAL ENGINEERING"61928], . . .‘‘‘

Limit

The previous query statement returned a lot of data, sometimes this is cumbersome, we need to make a limit. There is a limit statement in SQL to implement this function, and the limit statement is placed at the end of the query.

• The following statement returns the first five of the results:

SELECTFROM5;‘‘‘[["PETROLEUM ENGINEERING"], ["MINING AND MINERAL ENGINEERING"], ["METALLURGICAL ENGINEERING"], ["NAVAL ARCHITECTURE AND MARINE ENGINEERING"], ["CHEMICAL ENGINEERING"]]‘‘‘

• This example will return the first 10 employed>1000:

SELECTMajor fromRecent_gradsWHEREEmployed>10000LIMITTen;" "[["Major"], ["CHEMICAL ENGINEERING"], ["Mechanical ENGINEERING"], ["Electrical ENGINEERING"], ["Computer ENGINEERING"], ["AEROSPACE ENGINEERING"], ["Biomedical ENGINEERING"], ["INDUSTRIAL and manufacturing ENGINEERING"], ["General ENGINEERING"], ["Computer Science"], ["MANAGEMENT information SYSTEMS and STATISTICS"]]" "

Logical Operators

There are 6 comparison operations in the where statement, and the logical operation (Logical operators) is most commonly used by or and and, using logical operations to connect multiple comparison operations for more granular filtering.

and Operator

• The syntax for and is as follows:

SELECT [column1, column2,...] FROM [table1]WHERE [condition1] AND [condition2]

• The following statement shows the first 10 of the data (major,sharewomen,employed) of sharewomen>0.5** and **employed>10000 selected from Recent_grads

SELECT Major,ShareWomen,Employed FROM recent_grads WHERE ShareWomen>0.5 AND Employed>10000 LIMIT 10;

Or Operator

• The syntax for the or operation is as follows:

SELECT [column1, column2,...] FROM [table1]WHERE [condition1] OR [condition2]

• The following code indicates that the first 20 of the data (<=) of Edian >= 10000 or unemployed major,median,unemployed 1000 are selected from Recent_grads

SELECT Major,Median,Unemployed FROM recent_grads WHERE Median >= 10000 OR Unemployed <= 1000 LIMIT 20;

Grouping Operators

• If we want to get majors is engineering and the majority is women or unemployment rate is less than 5.1%, the data, query statement as follows, you can find that the SQL language is not cluster-case:

SelectMajor, Major_category, Sharewomen, Unemployment_ratefrom recent_gradswhere (major_category =' Engineering ') and(Sharewomen >0.5 orUnemployment_rate <0.051);" [[]Major", "Major_category", "Sharewomen", "Unemployment_rate"], ["Petroleum ENGINEERING", "Engineering", 0.120564344, 0.018380527], ["Metallurgical ENGINEERING", "Engineering", 0.153037383, 0.024096386], ["NAVAL ARCHITECTURE and MARINE ENGINEERING", "Engineering", 0.107313196, 0.050125313], "

• Another thing to note is the question of using parentheses, if we do not bracket the sentence major_category = ' Engineering ', the system will assume that we want to express the following, then the result will be wrong:

where‘Engineering‘and0.5or0.051

Practice Grouping Operators

select Major, Major_category, Employed, Unemployment_ratefrom recent_gradswhere (Major_category = ‘Business‘ or Major_category = ‘Arts‘ or Major_category = ‘Health‘) and (Employed > 20000 or Unemployment_rate < 0.051);

Order by

• The result returned by the SELECT statement is based on the relative position of the data in the database, such as the data above is ranked by rank. But sometimes the results we want to get are arranged according to our wishes, and this time we can use the order BY statement, the syntax of the order BY statement is as follows:

SELECT [column1, column2,...] FROM [table1]WHERE [conditions]..ORDER BY column1 [ASC or DESC]

• SQL is sorted alphabetically by standard, with ESC, from small to large.

SelectMajor fromRecent_gradsOrder  byMajor DesclimitTen;" "[["Major"], ["Zoology"], ["VISUAL and performing ARTS"], ["states History"], ["Treatment therapy Professions"], ["Transportation SCIENCES and TECHNOLOGIES"], ["Theology and religious Vocations"], ["TEACHER education:multiple levels"], ["STUDIO ARTS"], ["STATISTICS and decision science"], ["Special NEEDS Education"]]" "

Order Using Multiple Columns

• SQL can also be sorted using multiple columns, usually in the name order, sorted by last name, and then by first name, because last name has a lot of the same.

select [column1, column2..]from table_nameorder by column1 (asc or desc), column2 (asc or desc)

select Major_category, Median, Majorfrom recent_gradsorder by Major asc, Median desclimit 20;

SQL basic Syntax &sqlite