[SQL] basic SQL

Time:2020-2-26

SQL introduction

SQL is the abbreviation of structure query language, which is the RDBMS application language.

SQL history

In the early 1970s, Edgar Frank Codd (also known as Ted Codd) of San Jose, California Research Laboratory of IBM, published the application principle of data into tables (Codd’s relational algebra). In 1974, d.d.chamberlin and r.f. Boyce of the same laboratory developed a set of standard language – sequential (Structured English Query Language) in the development of relational database management system R for Codd’s relational algebra, and published a new version of SQL (called sequential / 2) on IBM Journal of R & D in November 1976. It was renamed SQL in 1980.
In 1979 Oracle first provided commercial SQL, and IBM also implemented SQL in DB2 and SQL / DS database systems.1

Ted Codd is the absolute database creatorrelation modelTuring Award, 1981. In the relationship modelBoyce-Codd Normal From(between the third paradigm and the fourth paradigm) also comes from his name.

System RIt is also absolutely important in the history of database. Especially when reading books, they are constantly mentioned.

SQL standard

In October 1986, American ANSI adopted SQL as the standard language of relational database management system (ANSI X3. 135-1986), which was later adopted as an international standard by international organization for Standardization (ISO).
In 1989, American ANSI adopted the SQL standard language of relational database management system defined in the report of ANSI x3.135-1989, called ANSI SQL 89, which replaces the version of ANSI x3.135-1986. This standard is adopted by the following organizations:
International Organization for Standardization (ISO), report “database language SQL with integrity enhancement” for ISO 9075-1989
The federal information processing standard publication (FIPS pub) 127
At present, all the major relational database management systems support some forms of SQL, and most of the databases comply with the ANSI sql89 standard at least.
ANSI SQL92 standard adds external connection on top of cross join and internal connection, and supports writing connection expression in from clause. Supports Union and intersection operations of sets. Case (SQL) expressions are supported. Check constraints are supported. Create a temporary table. Support cursor.
Support transaction isolation.1

About this article

This article uses PostgreSQL’s SQL syntax. Focus we focus onselect...from...whereThis read operation analyzes query (analytical query).
Datasets can be used directly at https://hyper-db.de/interface. In addition, write operations are not allowed on this page:insert, update, deleteTransactional query like this. Of coursecreate tableanddrop tableAnd not allowed.

Schema:
[SQL] basic SQL

[SQL] basic SQL

Download:
https://db.in.tum.de/teaching…

Schma and most SQL statements come from pro. Alfons Kemper, Ph.D.’s courseware and books.

Courseware:

  • https://db.in.tum.de/teaching…
  • https://db.in.tum.de/teaching…

Book: https://db.in.tum.de/teaching

Foundation SQL

  • Search for all the professors whose rang is C4:
select persnr, name
from professoren
where rang = 'C4'
  • For all professors, first rank rank in reverse order, and then rank name in positive order:
select persnr, name, rang
from professoren
order by rang desc, name asc
  • Get all the possibilities of professional’s rank:
select distinct rang
from professoren
  • Search for a professor in maeeutik:
select p.name, v.titel
from professoren p, vorlesungen v
where p.persnr = v.gelesenvon and titel = 'Maeeutik'
  • List the student’s name and the class the student has attended:
select s.name, v.titel
from studenten s, hoeren h, vorlesungen v
where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr
  • Search how many students listen to each class, consider the classes that no one listens, and sort them in reverse order:
select v.vorlnr, v.titel, count(h.matrnr) as num
from vorlesungen v left outer join hoeren h on v.vorlnr = h.vorlnr
group by v.vorlnr, v.titel
order by num desc 
  • Search for the union of the name of assistenten and the name of professional:
(select name
from assistenten)
union
(select name
from professoren)
  • Search for professors who don’t have any classes:
-- correlated sub-query
select p.name
from professoren p
where not exists(
    select *
    from vorlesungen v
    where v.gelesenvon = p.persnr
    )

-- un-correlated sub-query
--Not in: comparison of sets
select p.name
from professoren p
where p.persnr not in (
    select v.gelesenvon
    from vorlesungen v
    )
  • Search for students who don’t have any classes:
-- correlated sub-query
select s.name
from studenten s
where not exists(
    select *
    from hoeren h
    where h.matrnr = s.matrnr
    )

-- un-correlated sub-query
--Not in: comparison of sets
select s.name
from studenten s
where s.matrnr not in (
    select h.matrnr
    from hoeren h
    )
  • Students who listen to 3 or more courses
select s.matrnr, s.name
from studenten s, hoeren h
where s.matrnr = h.matrnr
group by s.matrnr, s.name
having count(*) >= 3
  • Search for semester’s largest students:
select s.name
from studenten s
where s.semester >= all (
    select semester
    from studenten
    )
  • Find the average semester of student:
select avg(semester)
from studenten
  • Search for SWS > = 4 and act as vorgaanger for at least two other courses:
select v.vorlnr, v.titel
from vorlesungen v, voraussetzen vor
where v.vorlnr = vor.vorgaenger and v.sws >= 4
group by v.vorlnr, v.titel
having count(*) >= 2
  • Search the sum of SWS for each professor’s class:
select v.gelesenvon, sum(v.sws)
from vorlesungen v
group by v.gelesenvon
  • Search for professionals with rank C4 whose total SWS is more than 3:
select v.gelesenvon, p.name, sum(v.sws)
from vorlesungen v, professoren p
where v.gelesenvon = p.persnr and rang = 'C4'
group by v.gelesenvon, p.name
having avg(v.sws) >= 3

You need to pay attention to the aggregation operation here.
A tuple is generated for each group. So for those withgroup byClauseSQLSentence,selectClause can only containgroup byThe attribute value and aggregation operation mentioned in clause.

  • Syntax sugar for comparison
SELECT *
FROM studenten 
WHERE semester >= 1 AND semester <= 4;

SELECT *
FROM studenten 
WHERE semester between 1 and 4;

SELECT *
FROM studenten 
WHERE semester in (1,2,3,4);
  • String comparison:
SELECT *
FROM studenten 
WHERE name like 'T%eophrastos';

SELECT DISTINCT s.name
FROM vorlesungen v, hoeren h, studenten s
WHERE s.matrnr = h.matrnr AND h.vorlnr = v.vorlnr AND v.titel LIKE '%thik%';
  • case
SELECT matrnr, 
            (case when note <= 1.5 then 'sehr gut'
               when note <= 2.5 then 'gut'
               when note <= 3.5 then 'befriedigend'
               when note <= 4.0 then 'ausreichend'
               else 'nicht bestanden'
            end)
FROM pruefen;
  • left outer join:
SELECT p.persnr, p.name, f.persnr, f.note, f.matrnr, s.matrnr, s.name
FROM professoren p left outer join pruefen f left outer JOIN studenten s ON f.matrnr = s.matrnr ON p.persnr = f.persnr;
  • right outer join:
SELECT p.persnr, p.name, pf.persnr, pf.note, pf.matrnr, s.matrnr, s.name
FROM professoren p right outer JOIN pruefen pf right outer JOIN studenten s ON pf.matrnr = s.matrnr ON p.persnr = pf.persnr;
  • full outer join:
SELECT p.persnr, p.name, pf.persnr, pf.note, pf.matrnr, s.matrnr, s.name
FROM professoren p full outer JOIN pruefen pf full outer JOIN studenten s ON pf.matrnr = s.matrnr ON p.persnr = pf.persnr;

  • Make a multiplication table
-- precompute look up table
with mtable as (values
( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
( 2, 4, 6, 8, 10, 12, 14, 16, 18, 20),
( 3, 6, 9, 12, 15, 18, 21, 24, 27, 30),
( 4, 8, 12, 16, 20, 24, 28, 32, 36, 40),
( 5, 10, 15, 20, 25, 30, 35, 40, 45, 50),
( 6, 12, 18, 24, 30, 36, 42, 48, 54, 60),
( 7, 14, 21, 28, 35, 42, 49, 56, 63, 70),
( 8, 16, 24, 32, 40, 48, 56, 64, 72, 80),
( 9, 18, 27, 36, 45, 54, 63, 72, 81, 90),
( 10, 20, 30, 40, 50, 60, 70, 80, 90, 100)
)
select * from mtable

perhaps

with mtable_column(a, b, c, d, e, f, g, h, i, k) as (values
(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) -- it's actually written in one line
),
mtable_row(x) as (values
(1) , (2), (3), (4), (5), (6), (7), (8), (9), (10) -- actually written as a column
)
select
a * x,
b * x,
c * x,
d * x,
e * x,
f * x,
g * x,
h * x,
i * x,
k * x
from mtable_column, mtable_row

  1. SQL Wikipedia. Https://zh.wikipedia.org/wiki A kind of

Recommended Today

Laravel service container must know

The article was forwarded from the professional laravel developer community. Original link: https://learnku.com/laravel/t To learn how to build an application with laravel is not only to learn how to use different classes and components in the framework, but also to remember allartisanCommand or all helper functions (we have Google). Learning to code with laravel is […]