SQL. Basic course. System administrator electronic library

56.

57.

58.

59.

60.

61.

62.

63.

64.

65.

66.

67.

68.

69.

70.

71.

72.

73.

74.

75.

76.

77.

78.

79.

80.

81.

82.

83.

84.

85.

86.

87.

88.

89.

90.

91.

92.

93.

94.

95.

96.

97.

98.

99.

100.

101.

102.

103.

104.

105.

106.

107.

108.

Transcript

2 Postgres Professional Company EP Morgunov SQL LANGUAGE. BASIC COURSE TRAINING AND PRACTICAL GUIDE Moscow 2017

3 UDC LBC M79 M79 Morgunov, EP SQL language. Basic course: study-practical. allowance / EP Morgunov; ed. E. V. Rogova, P. V. Luzanova; Postgres Professional. M., p. This practical tutorial is the first, basic, part of the SQL language training course offered by the Russian company Postgres Professional. The training material is presented with a view to using the PostgreSQL database management system. The manual can be used both under the guidance of a teacher and for independent study of the SQL language. The manual is intended for students studying in the areas of "Informatics and Computer Engineering", "Information Systems and Technologies", "Applied Informatics", "Software Engineering" and "Mathematical Support and Administration of Information Systems". It can be useful to a wide range of students and professionals who want to familiarize themselves with the basics of the SQL language in the PostgreSQL database management system environment. UDC BBK Postgres Professional, 2017 E. P. Morgunov, 2017

4 Table of contents Introduction 5 1 Introduction to databases and SQL What are databases and why they are needed Basic concepts of the relational model What is SQL Language description of the domain and training database Test questions and tasks Creating a working environment Installing a DBMS psql program PostgreSQL interactive terminal Deploying training Databases Test questions and assignments Basic operations with tables 22 Test questions and assignments Data types of PostgreSQL DBMS Numeric types Character (string) types Date / time types Boolean type Arrays JSON types Test questions and assignments Data definition language basics Default values \u200b\u200band restrictions Integrity Creating and Deleting Tables Modifying Tables Views Database Schemas Test Questions and Tasks Queries Additional Features of the SELECT Command Connections Aggregation and Grouping Subqueries Test Questions and Tasks Modifying Data Inserting Rows into Tables Updating Rows in Tables Deleting Rows from tables Test questions and tasks

5 8 Indexes General information Multi-column indexes Unique indexes Expression-based indexes Partial indexes Review questions and tasks Transactions Isolation level READ UNCOMMITTED Isolation level READ COMMITTED Isolation level REPEATABLE READ Isolation level SERIALIZABLE Example of using transactions Locks Test questions and tasks Improving performance Basic concepts Methods table scans Methods of forming rowset joins Scheduler management Query optimization Test questions and tasks Recommended sources 255 4

6 Introduction Currently, the term "database" is known to many people, even those far from professional development of computer programs. Databases have become a very widespread technology, which in turn has required more people to design and maintain them. In the course of the evolution of the theory and practice of databases, the relational data model has become the de facto standard, and within this model a specialized programming language has been formed that allows you to perform all the necessary operations with Structured Query Language (SQL) data. Thus, proficiency in the SQL language is an important component of a database professional's qualifications. This tutorial covers the basics of the SQL language in a basic course. Moreover, the language is considered in relation to a specific database management system (DBMS) PostgreSQL. The implementation of the SQL language in each DBMS complies with the standard to one degree or another, but in addition to standardized functions and capabilities, each DBMS also offers its own additional language extensions. PostgreSQL provides very good support for the SQL language standard and also provides interesting and useful additional features. One of the main advantages of PostgreSQL is extensibility. This means, for example, that a user (who is of course a database specialist) can develop their own data types. These data types will have all the properties of the built-in data types and can be put into operation without stopping the server. In addition, PostgreSQL is free and open source and is available on a wide variety of platforms. This tutorial covers not only all the basic SQL commands, but also other topics such as indexes and transactions. The manual has been written in such a way that it can be used both under the guidance of a teacher and independently. It is assumed that students have access to an already installed DBMS, so the PostgreSQL installation procedure is not covered in detail, but only instructions are given on where to find installation instructions. This tutorial is intended to provide you with practical skills in using the SQL language. The educational material is presented in such a way that the reader should receive many important knowledge as a result of completing the tasks at the end of each chapter. This knowledge may not be presented in the main text of chapters. It is assumed that a significant part of the tasks will be performed by the reader independently using the documentation for the PostgreSQL DBMS, but instructions for their implementation are often given. The tasks in the manual vary in difficulty. The most difficult ones, as well as those that take a long time to complete, are marked with an asterisk. The assignments can be completed as you study the training material for a particular chapter. However, some of them are complex in nature, so to complete them you need to study the entire chapter or at least several sections of it. five

7 Although the manual has a practical focus and is not a theoretical course, the first chapter briefly, at an elementary level, outlines the basic concepts of database theory and the relational model. This is done so that students can begin practical mastering of the SQL language without delay, from the first days of the academic semester, even before the moment when these concepts will be thoroughly considered in the lecture course. At the faculties of information technology in Russian universities, databases are traditionally studied in the second or third year. Moreover, this discipline, as a rule, takes one semester. However, the number of academic hours of study may vary. If the curriculum allocates 36 hours for practical classes in this discipline, then we recommend the following distribution of time for studying the material of the manual. Chapter 1. Introduction to databases and SQL Chapter 2. Creating a working environment Chapter 3. Basic operations with tables Chapter 4. Data types of PostgreSQL DBMS Chapter 5. Basics of the data definition language Chapter 6. Queries Chapter 7. Modifying data Chapter 8. Indexes Chapter 9. Transactions Chapter 10. Improving Performance 1 hour 1 hour 4 hours 4 hours 4 hours 8 hours 4 hours 2 hours 4 hours 4 hours Chapters 1 and 2 can be studied in one two-hour lesson, since PostgreSQL must already be installed in the classroom in advance. Chapter 3 is a brief overview of the basic features of the SQL language, after completing it, students should be able to imagine simple ways to use all the basic commands of the language. This chapter is not very difficult, but long, so you have four hours to study it. Chapter 4 discusses the basic data types used in PostgreSQL. This is a large chapter, but it contains a significant portion of activities and exercises. Students are expected to master only the basic techniques for using data types in four hours. And in order for the knowledge to be consolidated, it is recommended to refer to the material of this chapter (including the exercises) in the process of studying the remaining chapters of the manual, if it is necessary to clarify certain features of the use of specific types. We recommend distributing the time allotted for studying this chapter as follows: two hours for the first four paragraphs of numeric and string types, date / time and boolean types, another two hours for arrays and json / jsonb types. To execute queries to the database, you need to have a good understanding of its structure, the relationship of tables. Therefore, Chapter 5, which deals with the basics of the data definition language, is very important from the point of view of a detailed study of the tables of the Air Transportation database and preparation for mastering Chapter 6. Since the material of the chapter is based on the fact that the database is already deployed on the student's computer, then enter no commands are required to create tables. This allows you to reduce the time spent studying the chapter. This tutorial takes an approach that first looks at the data definition commands and then the data manipulation commands. Therefore, Chapter 5 "Data Definition Language Basics" precedes Chapter 6

8 chapter 6 "Requests". However, the chosen approach is not very rigidly implemented: in the overview chapter 3, basic commands, including simple queries, are considered. And queries are already a data manipulation language. Chapter 5 has four hours of study. During your first two-hour lesson, you will study the first two paragraphs, which cover topics such as integrity constraints and table creation and deletion. The second two-hour session should be devoted to the study of the three remaining paragraphs. They talk about ways to modify tables, as well as views and database schemas. Chapter 6 is the central chapter of the manual, so it takes eight hours to study, more than the other chapters. It consists of four sections. The first one deals with the various additional features of the SELECT command. This includes things like the LIMIT and OFFSET clauses, the LIKE operator, and regular expressions in the WHERE clause, and more. Nevertheless, the material of this paragraph is not difficult, it is enough to allocate one hour to study it. The second paragraph explains how to join tables. This is a more complex topic and requires two hours to study. The third paragraph deals with aggregation and grouping. It also covers such an important and interesting topic as window functions. This paragraph also requires a two hour lesson. The most difficult section of this chapter is the fourth. It is about subqueries. In particular, it highlights such an important and interesting topic as Common Table Expressions (CTE). To study the material in this section, you need to allocate three hours. Chapter 7 contains all the commands for changing data: inserting rows, updating them, and deleting them. Since these commands have already been used for simple tasks in the previous chapters, this chapter discusses more advanced ways to use them. There are many exercises in it, they make up half of its volume. We recommend that you spend two hours exploring how to insert rows into tables, and another two hours looking at how to update and delete rows. Chapter 8 is about indexes and is small so you can get familiar with it in one two-hour lesson. Since indexes are closely related to performance issues, that is, query speed, it would be advisable, after studying the final chapter, to return to Chapter 8 and look at the commands and queries presented there, already knowing about the EXPLAIN command. Chapter 9 is devoted to transactions. The mechanisms for their execution have many subtleties, so while studying this chapter you need to experiment and try to explain the results. The final chapter 10 discusses productivity issues. This chapter may seem too abstract and complex for a beginner SQL course, but it is very important nonetheless. Students should learn to read query execution plans and understand the purpose of each operation presented in the plan. And mastering the art of query optimization will take a lot of time and experience, and it won't come right away. In the event that 54 hours are allocated for practical training in the discipline "Databases" in the curriculum, you can change the proposed distribution of teaching hours. In particular, in Chapter 4 you can devote more time to data types 7

9 json / jsonb and arrays. Chapter 6 provides a more detailed look at window functions and common table expressions. In Chapter 9, on transactions, it might be worthwhile to develop a simple application that uses transactions and experiment with that application by running multiple sessions in parallel and changing the transaction isolation levels. As part of Chapter 10, it makes sense to go back to the commands and queries in Chapter 8 and examine the plans for executing them using the EXPLAIN command. Due to the extra time, all tasks and exercises of increased difficulty (marked with an asterisk) can be considered. Thus, the distribution of time can be as follows: Chapter 1. Introduction to databases and SQL Chapter 2. Creating a working environment Chapter 3. Basic operations with tables Chapter 4. Data types of PostgreSQL DBMS Chapter 5. Basics of data definition language Chapter 6. Queries Chapter 7. Modifying data Chapter 8. Indexes Chapter 9. Transactions Chapter 10. Improving productivity 1 hour 1 hour 4 hours 6 hours 6 hours 12 hours 6 hours 4 hours 8 hours 6 hours The tutorial uses different types of fonts to highlight text fragments depending on their destination. Commands entered by the user in both the operating system and the psql utility are in bold monospaced font. For example: psql -d demo -U postgres or SELECT avg (total_amount) FROM bookings; The results of operating system commands and SQL commands executed in the psql utility are printed in monospaced font. For example, in response to EXPLAIN SELECT * FROM aircrafts; The screen will display the following: QUERY PLAN Seq Scan on aircrafts (cost \u003d rows \u003d 9 width \u003d 52) (1 line) We hope that studying the material in this tutorial will help you improve your skills and broaden your professional horizons. 8

10 1 Introduction to Databases and SQL This chapter is introductory. In it, we'll cover the basics of databases, what the relational model is, and why you need SQL. A very important topic in this chapter will be the description of the subject area, on the basis of which the training database will be designed, which will serve as a platform for learning the SQL language. This tutorial is primarily intended for practical mastering of the SQL language, and not for studying database theory, therefore, to study the theory, you must consult the authoritative sources, a list of which is given at the end of the tutorial. 1.1 What Databases Are and Why You Need Them Database technologies have not always existed. However, even before their introduction into practice, people also collected and processed data. One of the ways to store data was the so-called flat files, which had a very simple structure: the data was stored as records, divided into fields of fixed length. In real life, complex relationships often arise between data elements, which must be transferred to an electronic database. When using flat files, these relationships are difficult to organize, and even more difficult to maintain them when changing or deleting individual data items. One of the basic concepts in database theory is the data model. We can say that it characterizes the way data is organized and the main methods of accessing it. First, hierarchical and network data models were proposed. However, in the course of the evolution of theories and ideas, a relational data model was developed, which is now dominant. Therefore, databases of the relational type prevail now. Their characteristic feature is the fact that the data is perceived by the user as tables. The user has operators for fetching data from tables, as well as for inserting new data, updating and deleting existing data. One of the advantages of a relational database is its ability to maintain relationships between data elements, eliminating the need for a programmer to do this routine and very time-consuming work. Back in the days before relational database technologies were widely adopted, programmers had to manually implement in procedural languages \u200b\u200bwhat are now called cascading foreign key updates or cascading records from subordinate tables (files). Here, the word "manually" means that to perform these operations, you had to write code, consisting of elementary commands, allowing you to get to each updated or deleted record. That approach to working with databases was called a navigational programmer who pointed the program to a specific algorithm for finding records. Let's give as an example a simple situation: in a database built on the basis of files, information about students and their exam grades is stored, moreover, the personal data of students are stored in one file, let's call it conditionally "Students", and exam grades in another file, which let's call it "Estimates". If you want to delete information about a specific student and his exam 9

11 grades, you will not only have to perform the operation of deleting a specific record from the "Students" file, but additionally organize a loop to find and delete those records from the "Grades" file for which the key field has the same value as the field in the deleted record from file "Students". Working with relational databases, the programmer is relieved of programming at the "atomic" level, because modern languages \u200b\u200bfor "communicating" with these databases are declarative. This means that in order to obtain a result, it is enough only to indicate what needs to be obtained, but it is not required to prescribe a method for obtaining the result, that is, how to obtain it. The database system is a computerized system for storing, processing and issuing information at the request of users. Such a system includes software and hardware, data itself, and users. Modern database systems are typically multi-user. In such systems, several users can simultaneously access the database. The main software is the database management system. In English it is called database management system (DBMS). In addition to the DBMS, the database system may include utilities, tools for developing applications (programs), database design tools, report generators, etc. Users of systems with databases are divided into a number of categories. The first category is application programmers. The second category is the end users for whom all the work is done. They can access the database using application programs or generic applications that are included in the software of the DBMS itself. Most DBMSs have a so-called query language processor that allows the user to enter commands in a high-level language (such as SQL). The third category of users is database administrators. Their responsibilities include: creating a database, choosing the optimal modes of access to it, differentiating the authority of different users to access certain information in the database, performing a database backup, etc. The database system can be divided into two main components : a server and a set of clients (or external interfaces). The server is the DBMS. Clients are various applications written by application programmers or embedded applications supplied with the DBMS. One server can serve many clients. Modern DBMSs include a data dictionary. This is the part of the database that describes the actual data stored in it. The data dictionary helps the DBMS perform its functions. 1.2 Basic concepts of the relational model Each technology area has its own terminology. There are basic terms on which all further reasoning is based. Such terms mean 10

12 are also present in the field of databases. Now we will briefly talk about them. In the era prior to the birth of relational theory, databases were traditionally viewed as a collection of files of records, and records, in turn, were subdivided into separate fields. The field was an elementary data unit. In relational databases, the user perceives data as tables. Therefore, the term “file” corresponds to the term “table”, instead of the term “record” the term “row” is used, and instead of the term “field” the term “column” (or “column”). Thus, tables consist of rows and columns, at the intersection of which there must be "atomic" values \u200b\u200bthat cannot be broken down into smaller elements without losing meaning. In the formal theory of relational databases, these tables are called relations, therefore databases are called relational. Relationship is a mathematical term. When determining the properties of such relations, set theory is used. In terms of this theory, the rows of the table will be called tuples, and the columns are called attributes. A relation has a header, which is made up of attributes, and a body, which is made up of tuples. The number of attributes is called the degree of the relationship, and the number of tuples is called the cardinal number. In addition to set theory, one of the foundations of relational theory is such a branch of mathematical logic as predicate calculus. Thus, in the theory and practice of databases, there are three groups of terms. Sometimes terms from different groups are used interchangeably, such as notation and string. As we said above, in relational databases, the user perceives data in the form of tables. Consider a simple system with only two tables. The first "Students": grade book Full name Document series Document number Ivanov Ivan Petrovich Klimov Andrey Ivanovich Novikov Nikolai Yurievich And the second "Progress": Grade book Subject Academic year Semester Assessment Physics 2016 / Mathematics 2016 / Physics 2016 / Physics 2016 / When working with databases, you often have to follow various restrictions, which may be due to the specifics of a particular subject area. Simplifying the real situation, we will accept the following restrictions: 11

13 the gradebook number consists of five digits and cannot be negative (different universities use different schemes for assigning numbers to gradebooks, these schemes can be much more complicated than the one adopted by us and can take into account, for example, the year a student entered the university); the series of the identity document is a four-digit number, and the number of the identity document is a six-digit number; semester number can take only two values \u200b\u200b1 (fall semester) and 2 (spring semester); the mark can take only three values \u200b\u200b3 (satisfactory), 4 (good) and 5 (excellent): it is not accepted to put other marks in the record books. To identify rows in tables and to link tables together, so-called keys are used. A potential key is a combination of table attributes that uniquely identifies rows in a table. The key can consist of only one attribute of the table. For example, in the table "Students" such an identifier can be the attribute "Gradebook number". Two of its attributes taken together can also serve as a potential key for this table: "Series of identity document" and "Number of identity document". None of them alone can be used as a unique identifier. In this case, the key will be composite. In this case, it is important that the potential key should not be redundant, that is, no subset of the attributes included in it should have the uniqueness property. A potential key that includes the two mentioned attributes is not redundant. Keys are needed for addressing at the row (record) level. If there is more than one potential key in the table, one of them is selected as the so-called primary key, and the rest will be alternative keys. Consider the tables "Students" and "Performance". Suppose that the "Students" table does not contain a row with the gradebook number 55900, then it makes no sense to include a row with this gradebook number in the "Grades" table. Thus, the values \u200b\u200bof the "Gradebook number" column in the "Grades" table must be consistent with the values \u200b\u200bof the same column in the "Students" table. The Gradebook Number attribute in the Grades table is an example of what is called a foreign key. The table containing the foreign key is called the referencing table. The table containing the corresponding candidate key is called the referenced table. In such cases, the foreign key is said to refer to the candidate key in the referenced table. A foreign key can be composite, that is, it can include more than one attribute. The foreign key does not need to be unique. The problem of ensuring that the database does not contain invalid foreign key values \u200b\u200bis known as a referential integrity problem. The constraint that foreign key values \u200b\u200bmust match the candidate key values \u200b\u200bis called a referential integrity constraint (referential constraint). The DBMS is responsible for enforcing the referential integrity constraints, and the developer is only required to specify the attributes that serve as external 12

14 keys. In database design, it is often envisaged that when a row is deleted from a referenced table, the corresponding rows from the referenced table must also be deleted, and when the value of a column referenced by a foreign key changes, the foreign key values \u200b\u200bin the referenced table must change. This approach is called cascading delete (update). Other approaches are sometimes used. For example, instead of deleting rows from the referenced table, these rows simply replace the values \u200b\u200bof the attributes included in the foreign key with so-called NULL values. These are special values \u200b\u200bthat mean "nothing" or no value, they do not match the value "zero" or "empty string". NULL is used in databases and as a default value when no specific value is entered by the user. Primary keys cannot contain NULL values. Transaction is one of the most important concepts in database theory. It means a set of operations on a database, considered as a single and indivisible unit of work, performed completely or not at all, if there was some kind of failure during the transaction. Thus, transactions are a means of ensuring data consistency. In our database, a transaction can be, for example, two operations: deleting a row from the "Students" table and deleting foreign-key related rows from the "Grades" table. 1.3 What is SQL Language SQL is a non-procedural language that is the standard means of working with data in all relational DBMSs. Operators (commands) written in this language only indicate to the DBMS what result should be obtained, but do not describe the procedure for obtaining this result. The DBMS itself determines how the user command is executed. In the SQL language, there is traditionally a group of data definition statements (Data Definition Language DDL), a group of data manipulation operators (Data Manipulation Language DML), and a group of operators that manage access privileges to database objects (Data Control Language DCL). Data Definition Language (DDL) statements include commands for creating, modifying, and deleting tables, views, and other database objects. Chapters 5 and 8 are devoted to these commands in detail. Data Manipulation Language (DML) statements are commands for fetching rows from tables, inserting rows into tables, updating and deleting rows. These commands are discussed in detail in Chapters 6 and 7. DCL operators are not covered in this tutorial, since PostgreSQL allows you to do without using them at the initial stage of learning SQL. thirteen

15 1.4 Description of the subject area and the sample database To show all the basic features of the SQL language, we need a database. This database shouldn't be too complex or take too long to learn. But, at the same time, it must be diverse enough so that requests to it would look plausible, almost the same as in real work. We will choose passenger air transportation as a subject area. Its original description and the description of the Air Transport database can be found at the addresses and ru / docs / postgrespro / current / demodb-bookings.html. Hopefully this area will be familiar to many readers of our tutorial. Of course, for educational purposes, the real situation is intentionally simplified, but all the fundamental things are preserved. So, a certain Russian airline performs passenger air transportation. It has its own fleet of aircraft of various models. Each aircraft model has a specific code assigned by the International Air Carriers Association (IATA). In this case, we will assume that airplanes of the same model have the same cabin layouts, i.e., the order of seating and numbering of seats in the business class and economy class cabins. For example, if it is a Sukhoi SuperJet-100 model, then the 2A seat belongs to the business class, and the 20D seat belongs to the economy class. Business class and economy class are variations of the so-called service class. Our airline operates flights between Russian airports. Each airport is assigned a unique three-letter code, using only capital letters of the Latin alphabet. These codes are not assigned by the airline itself, but by special organizations that manage passenger air transportation. Often the name of the airport does not coincide with the name of the city to which this airport belongs. For example, in the city of Novosibirsk the airport is called Tolmachevo, in the city of Yekaterinburg Koltsovo, and in St. Petersburg Pulkovo. In addition, some cities have more than one airport. Immediately as an example, I recall Moscow with its airports Domodedovo, Sheremetyevo and Vnukovo. Let's add one more important detail: each airport is characterized by geographical coordinates, longitude and latitude, as well as a time zone. Flight routes between cities are being formed. Of course, each such route requires indicating not only the city, but also the airport, since, as we said, there can be more than one airport in a city. As a simplification of reality, we decide that the routes will not have stopovers, i.e. That is, they will only have an airport of departure and an airport of destination. Each route has a six-digit number, including numbers and letters of the Latin alphabet. Based on the list of routes, a flight (or flight) schedule is formed. The timetable shows the planned departure time and the planned arrival time, as well as the type of aircraft performing this flight. When the flight is actually performed, it becomes necessary to take into account additional information, namely: the actual time of departure and the actual time of arrival, as well as the status of the flight. Flight status can take a number of values: 14

16 Scheduled (the possibility of booking opens per month); On Time (registration opens a day before); Delayed (flight delayed); Departed (departed); Arrived (arrived); Canceled. Now let's turn to the passengers. The flight begins with booking an air ticket. It is now common practice to issue electronic tickets. Each such ticket has a unique 13-digit number. Within one booking procedure, several tickets can be issued, but each such procedure has a unique six-digit booking number (code), consisting of capital letters of the Latin alphabet and numbers. In addition, for each booking procedure, the booking date is recorded and the total cost of tickets issued is calculated. Each ticket, in addition to its thirteen-digit number, contains the passenger's identifier, as well as his first and last name (in Latin transcription) and contact information. The number of the identity document is used as the passenger identifier. Of course, a passenger can change his document, and sometimes even his last name and first name, during the time elapsed between booking tickets on different days, so it is impossible to say for sure that any specific tickets were issued for the same passenger. Each e-ticket can contain more than one flight. Experts call these flight records segments. An example of the presence of several segments is the following: Krasnoyarsk Moscow, Moscow Anapa, Anapa Moscow, Moscow Krasnoyarsk. At the same time, it is possible to issue several tickets for different passengers within the same booking. For each flight, the flight number, airports of departure and destination, departure and arrival times, and the cost of the flight are indicated. In addition, the so-called class of service is indicated: economic, business, etc. When a passenger arrives at the airport of departure and passes the registration of a ticket, a so-called boarding pass is issued. This coupon is associated with an air ticket: the coupon contains the same number as the electronic ticket of this passenger. In addition, the ticket contains the flight number and seat number on the plane. The number of the boarding pass is also indicated, the sequential number assigned during the check-in process for this flight. Let us remind you that each seat in the aircraft cabin corresponds to a specific class of service. This information is taken into account when registering tickets and issuing boarding passes. If, for example, a passenger has purchased a ticket with an economy class of service, then his boarding pass will indicate the seat number in the economy class cabin, but not in the business class cabin. Following the above description of the subject area, you can design a model database intended for learning the SQL language. Since our tutorial is primarily about learning the SQL language, not mastering the art of database design, we will only show you Figure 15

17 Bookings Airports Bookings Airports # book_ref * book_date * total_amount # airport_code * airport_name * city * longitude * latitude * timezone Tickets Tickets # ticket_no * book_ref * passenger_id * passenger_name contact_data Ticket_flights Flights # ticket_no # flight_id * flight amount_conditions_ Flights * Flights # * scheduled_departure * scheduled_arrival * departure_airport * arrival_airport * status * aircraft_code actual_departure actual_arrival Aircrafts Airplanes # aircraft_code * model * range Boarding_passes Boarding passes Seats Seats # ticket_no # flight_id * boarding_no * seat_no # aircraft_code # seat_no * fare_conditions, which show the selected scheme data in the subject area, as well as their relationships and attributes. The specific data types, primary and foreign keys, as well as the restrictions imposed on attributes and tables, we will show in the following chapters in the process of looking at SQL commands designed to physically create tables in a database. This diagram can be found on the Postgres Professional website at the addresses listed at the beginning of this section. Test questions and tasks 1. What groups of operators stand out in the SQL language? 2. Give an informal definition of the basic concepts of the relational data model: relation, tuple, attribute. 3. What are foreign keys in relational tables for? 4. What is a potential key? 5. * Provide an example of a redundant candidate key for one of the tables in the Air Transportation database and explain why it would be redundant. sixteen

6. * In the current implementation of the Air Transportation database, it is assumed that aircraft of the same model can have only one cabin layout. Imagine that management has decided to consider the possibility of having different layouts for each model. Which tables will need to be modified in this case and how? Do you need to create additional tables? 17

19 2 Setting up a working environment Before you start learning SQL directly, you need to access the PostgreSQL server. This can be done, for example, in a computer lab or by accessing a remote server via a terminal. However, you can create a working environment for yourself and on your local computer by installing the full version of PostgreSQL DBMS, that is, the server and client programs. In this case, you will have much more authority to configure and use PostgreSQL. In the final part of this chapter, we will show you how to deploy the Air Transport training database filled with specially prepared plausible data. 2.1 Installing the Database Since this tutorial is about learning the SQL language and not the basics of administering PostgreSQL, we will only give you a quick guide on where to find installation instructions. You need to start by choosing the DBMS distribution kit that you would like to install. You can choose the original PostgreSQL option or the one offered by Postgres Professional. It is called Postgres Pro and contains not only all the functions and modules that come with the standard distribution, but also additional work done by Postgres Professional. These distributions are equally suitable for learning the basics of the SQL language. However, Russian documentation is included only with PostgreSQL. After you have decided on a specific DBMS distribution kit, you need to select an operating system. PostgreSQL supports many systems, including various versions of Linux as well as Windows. It is recommended to install the latest stable version of the DBMS. If you decide to use the original PostgreSQL distribution, you can find instructions on how to install it on various operating systems at If you opted for a PostgreSQL distribution, then you should go to latest. After installing both PostgreSQL and Postgres Pro on Windows, additional steps will have to be taken to ensure that the use of the Russian alphabet in psql interactive terminal does not cause problems. The psql utility is discussed in the next section. The installation process will create a DBMS user account named postgres. You do not need to create additional accounts to study this tutorial. eighteen

20 Having installed one or another PostgreSQL distribution kit, you need to learn how to start the database server, because otherwise it is impossible to work with data. How to do this is described in detail in the documentation in section 18.3 "Starting the database server". You can find this section at current / server-start.html. When installing the DBMS in the Windows environment, a service is created to automatically start the PostgreSQL server when the operating system boots. Having finished working with the server, you need to correctly stop (turn off) it. The order of actions in such a situation is described in the documentation in section 18.5 "Server shutdown". You can find this section at postgresql / 9.6 / server-shutdown.html. 2.2 The psql program PostgreSQL interactive terminal To access the database server, PostgreSQL comes with the psql interactive terminal. To run it, you need to enter the psql command. When you run the psql utility in Windows, the letters of the Russian alphabet may not be displayed correctly. To eliminate this, you need to change the font to Lucida Console in the properties of the window in which psql is running, and use the chcp command to change the current code page to CP1251: chcp 1251 In the psql utility environment, you can enter not only SQL commands, but also various service commands, supported by the utility itself. To get a quick reference for all service commands, enter \\? Many of these commands begin with \\ d characters. For example, to view a list of all tables and views created in the database to which you are currently connected, enter the command \\ dt.If you are interested in the definition (simply, structure) of a specific database table, for example, students, you need to enter the command \\ d students To get a list of all SQL commands, you need to execute the command \\ h To display a description of a specific SQL command, for example, CREATE TABLE, you need to do this: \\ h CREATE TABLE 19

21 This utility allows you to reduce the amount of manual input by complementing the command input with psql. For example, when entering an SQL command, you can use the Tab key to complete the entered command keyword or database table name. For example, when you enter the CREATE TABLE ... command, you can enter the characters "cr" and press the Tab key psql will complete this word before "create". You can do the same with the word TABLE. To enter it, just enter the letters "ta" and press the Tab key. If you enter too few letters for psql to uniquely identify the keyword, padding will not occur. But in this case, you can press the Tab key twice and get a list of all keywords starting with the letter combination you entered. 2.3 Deploying the Sample Database After completing the database server installation, we can move on to the question of how to deploy the Air Transport sample database prepared by Postgres Professional to your PostgreSQL cluster. The company's website has a section dedicated to this database, you can find it by the link It is provided in three versions, differing only in the amount of data: the most compact version contains data for one month, the medium-sized version covers a time period of three months, and the most complete version includes data for an entire year. All data were generated using special algorithms to ensure their “plausibility”. We recommend that you start with the compact version of the Air Transport database, and after gaining some experience in writing SQL queries, you will install the full version and already on it you will be able to better "feel" various subtleties of working with large data volumes, for example, evaluate the effect of indexes on speed data access. As a first step to deploying your database, you need to download a zipped backup from the zip link. Then you need to extract the file from the archive: unzip demo_small.zip The extracted file is called demo_small.sql. We will now create a database named demo in your PostgreSQL cluster. The shortest version of the command will be: psql -f demo_small.sql -U postgres If you want to redirect the output of messages generated by the DBMS during operation from the screen to files, you can do this: psql -f demo_small.sql -U postgres\u003e demo.log 2\u003e demo.err You can separate stdout and stdout for errors. Normal messages will be redirected to the demo.log file, and error messages to the demo.err file. Note that there must be no space between the number 2, which denotes the descriptor for standard error message output, and the\u003e sign, which denotes output redirection. 20

22 If it is more convenient for you to collect all messages into one common file, then you need to do this: psql -f demo_small.sql -U postgres\u003e demo.log 2\u003e & 1 Note that the entire expression 2\u003e & 1 at the end of the command is written without spaces. It tells the operating system to route error messages to the same place as normal messages. If our SQL file were very large, then it would be possible to execute the command in the background by putting an "&" at the end of the command line, and watch the progress in real time using the tail command. psql -f demo_small.sql -U postgres\u003e demo.log 2\u003e & 1 & tail -f demo.log Select one of the command options for database deployment and run this command. Everything is ready! You can connect to the new database: psql -d demo -U postgres Security Questions and Tasks 1. Complete the PostgreSQL installation procedure on the operating system of your choice. 2. Familiarize yourself with the psql utility using the built-in help, as well as using the help called by the psql --help command. 3. In addition to psql, there are other general-purpose programs for working with the PostgreSQL database server, for example, pgadmin. It is a powerful graphical utility. Install the pgadmin program yourself and learn the basics of working with it. 4. Deploy the sample database. Try to connect to it using psql utility. Use the \\ q command to exit the utility. 21

23 3 Basic Operations with Tables The SQL language is very diverse, it includes a number of commands, which, in turn, sometimes have many parameters and keywords. But let's start with a quick overview of the main features of the SQL language. In this chapter, you will learn how to enter data into a database, master the basic methods of obtaining information from a database, that is, sampling, and also learn how you can make changes to information stored in a database and delete data that is no longer are needed. There is a good tradition in the practice of learning foreign languages. Already in the first lesson, the student learns some basic grammatical constructions and words that allow him to say a few of the simplest, but, nevertheless, practically useful phrases. We will follow this tradition. In this section of our tutorial, you will learn the basic SQL commands that will allow you to perform basic operations. You will learn more complex (and interesting) commands in the following chapters. Let's say a few words about our approach to work. In principle, there are two possible ways of organizing the work of a student (learner). The first way is as follows: the student uses a database, which already contains all the necessary tables and other database objects, prepared in advance by the author of the textbook or other qualified specialist. At the same time, a certain set of necessary data has already been entered into the tables, so you can immediately proceed to executing queries on these tables. The described method seems very attractive because it requires less effort at the initial stage of mastering the SQL language. However, in our opinion, another method is more correct. Probably, it is more time consuming, but when you use it, you get a better feel, as they say, of the process of creating tables and entering records into these tables. And by executing various queries to the database, it will be easier for you to assess the correctness of the result of the query execution, since you entered all the data yourself and therefore can reasonably assume what results you expect to see on the screen. Of course, the first method can be very useful when learning more complex, advanced, features of the SQL language, which are difficult to understand without using large data sets, and it is not rational to manually enter large data sets into the database. It will be much more rational to generate them automatically by software. In Chapter 1, we described the subject area, so now we can start directly creating tables in the database. To execute all subsequent commands and operations, we will use the psql utility included in the standard PostgreSQL delivery. The demo database should already be deployed on your computer. The process for creating it is described in Chapter 2. Now run the psql utility and connect to this database using the postgres user account: psql -d demo --U postgres Use the CREATE TABLE command to create tables in SQL. Its full syntax is presented in the PostgreSQL documentation, and the simplified syntax is: 22

Postgres Professional E. P. Morgunov PostgreSQL. Basics of the SQL language Tutorial St. Petersburg "BHV-Petersburg" 2018 UDC 004.655 BBK 32.973.26-018.2 M79 Morgunov, E. P. M79 PostgreSQL. The basics

SQL language Lecture 6 Indices E. P. Morgunov Siberian State University of Science and Technology named after Academician M. F. Reshetnev Krasnoyarsk Institute of Informatics and Telecommunications [email protected]

Bratchikov I.L. 41. Basics of database design. Methods for building a DBMS. SQL, implementation examples. Fundamentals of modern databases. 1. Basic definitions. Terminology. In the literature you can find

Topic 10. Basics of database technology Database technology is one of the key achievements of informatics, used to build computer information systems and applications that implement information

BASIC CONCEPTS OF DATABASES 1. Choose the correct procedure for designing a database a) Solving the problem of data transmission b) Analysis of the subject area, taking into account the requirements of end users c)

Introduction This book is intended for developers who use Oracle as their database management system (DBMS). Therefore, this book does not devote much space to consider questions

Option 1 Choose the correct answer. There is only one possible correct answer. 1. An information system is a) Any information processing system b) A textual information processing system

SQL language Lecture 3 Basics of data definition language E. P. Morgunov Siberian State University of Science and Technology named after academician M. F. Reshetnev Krasnoyarsk Institute of Informatics and Telecommunications

SQL language Lecture 5 Data modification E. P. Morgunov Siberian State University of Science and Technology named after Academician M. F. Reshetnev Krasnoyarsk Institute of Informatics and Telecommunications [email protected]

➀ Information systems and data banks. The most important condition for ensuring the effective functioning of any organization is the presence of a developed information system. An information system is a system

Chapter 1. Getting Started How a Microsoft Access Database Works Designing a Data Structure Database Steps Getting Started Access 2007 User Interface Customizing Items

Chapter 3. Installing and running the multi-user version Requirements and conditions ... 1 How to install SBiC ++ on the network ... 2 Installing and configuring the Pervasive.SQL server and client ... 3 Installing the program ... 4 Preparation

BANK OF TEST TASKS Academic discipline Direction Level of preparation Department B1.B.12 Data management 09.03.02 Information systems and technologies undergraduate Methods and instruments of measurement and automation

Databases The development of computing technology has been carried out in two main areas: the use of computing technology to perform numerical calculations; use of computer technology

Bauman Moscow State Technical University Kaluga Branch Yu. E. Gagarin, S. V. Ponomarev APPLICATION OF SQL LANGUAGE IN MS ACCESS Teaching aid UDC 681.3.06 LBC 32.973

1. Information and data 2. Basic concepts of systems with databases Information computer systems with databases are systems of information, mathematical, software, language, organizational

PDM STEP SUITE version 5.0 Installation Instructions PDM STEP Suite Instructions for the system programmer. Part 1 JSC SRC "Applied Logistics" 2019 PDM STEP Suite v.5.0. System programmer instruction

1 of 6 Lecture 1. Database system architecture. Functions of the database system administrator in the corporate information system. Database objects. 1.1. ANSI / SPARC Database System Architecture ... 1

CONTENTS INTRODUCTION ... 3 1 RESEARCH OF THE SUBJECT AND STATEMENT OF THE PROBLEM ... 4 2 BUILDING AN INFOLOGICAL MODEL ... 5 3 DATALOGICAL DESIGN OF THE DATABASE ... 6 3.1 Choosing a control system

In modern conditions, when automating enterprises, one has to deal with different and often diametrically opposite requirements for accounting for the same accounting sections. According to the documentation

Educational discipline "Databases and their management" for students of the specialty Applied Informatics (bachelor's degree) 080800.62 Lecture 15 MODELS OF ORGANIZATION OF ACCESS TO DATABASES Educational questions: 1. Architecture

SQL Language Lecture 7 Transactions E. P. Morgunov Siberian State University of Science and Technology named after Academician M. F. Reshetnev Krasnoyarsk Institute of Informatics and Telecommunications [email protected]

Computer information technologies Module 13. General characteristics of Access DBMS 1 MODULE OBJECTIVES After studying the module you will be able to: tell about the functionality of the Access DBMS; know the features

Electronic Document Management System Arkhivaris Installation Manual Moscow, 2008 2 ABSTRACT This document describes the delivery set of the software product "EVFRAT-Document Management

DATABASES (DB). DB CONTROL SYSTEMS General provisions The purpose of any information system is to process data about objects in the real world. In the broad sense of the word, a database is a collection of information about

Introduction to databases. Introduction to SQL. Sumy Educational Center Software Quality Assurance (QA) Netcracker 2016 1 Learn about the following What is a database Database classification DBMS Clients DBMS

1.1. Basics of working with databases 1.1.1. Databases and database management systems. Data models Database (DB) is a named collection of structured data related to a certain

It seems that it was quite recently, but what significant changes have occurred since then! While the author was preparing to print the Professional SQL Server 7.0 Programming book in early 1999

Introduction to database theory Lecture 1 Lecture outline Basic concepts Data organization in a database Types of data models Database architecture Database classification Server 2008. Lecture 1 2 Basic concepts Server 2008. Lecture

Appendix 4 Subsystem of processes and computations control CONTENTS 1. General information ... 2 2. Modeling of states and working processes ... 2 2.1. State group list management ... 2 2.2. Creature

Softacom CRM Installation Guide Version 2.1. 1 "Softacom CRM Installation Guide". Contains a description of the system installation process. Contents 1 INTRODUCTION ... 3 1.1 General about the system ... 3 1.2 Features

Laboratory work 4 "Creating relationships between tables" Purpose of work: studying the technology of creating the structure of a relational database in Microsoft Office Access 2007 1. Brief theoretical information Word

Logical backups Topics Logical and physical backups Copying individual tables Backing up and restoring databases and a cluster 2 Types of backups Logical backups

MICROSOFT ACCESS: LINKS, CALCULATED FIELDS, CREATING A BUTTON FORM OF A DATABASE I. TABLE RELATIONS Modern databases usually consist of many tables linked together. One of the goals of creating a good structure

NATIONAL AVIATION UNIVERSITY Department of Applied Informatics Coursework Topic: Microsoft Access DBMS Creation and processing of databases Purpose of work: theoretically and practically master the basic techniques of work

Transactions and Concurrent Access: Comparing PostgreSQL and Oracle Implementations Egor Rogov, Postgres Professional Why All This? query result Why all this? query result Transactions Atomicity all

@ DB, DBMS 1. A data structure characterized by subordination of lower-level objects to upper-level objects is called A. tabular B. relational * C. hierarchical D. network 2. Distinctive

VOGBIT Installation Guide 2010 Contents Introduction ... 4 Hardware and Software Requirements ... 5 Installation Sequence ... 6 Installing the Software ... 7 Requesting and Obtaining a License ...

Stages of database development As a rule, non-professionals work with databases, so the following requirements for a database can be formulated. Developers, when creating a database, should focus on these

Database management system Access Completed by Chibinova Nazlygul Niyazovna, student of the Faculty of Foreign Languages \u200b\u200bof the Yelabuga Institute of the Kazan (Privozhsky) Federal University. Scientific

Informatics Lecture 5 MS Access Information systems Applications for storing, replenishing, editing and using large amounts of data Under use means in the simplest case access

Table of contents Preface ... 3 Part I. Databases, DBMS and data models Chapter 1. Purpose of database technology. Functions and main components of database management systems ..........................................

Laboratory for monitoring and automation of the educational process. Administrator's Guide for the Educational Process Support Site Revision 3 of 12/18/15. Korotkov D.S., Zhuchok I.O. Enable / Disable

Chapter 6 Database Theory 6.1. General concepts 6.2. Data Models 6.3. Relational databases 6.4. Post-relational models and databases 6.5. Database design Modern information systems,

Lab 9 Exploring the ability of Microsoft Access 2007 to generate queries using SQL. Purpose: 1. To study the order of forming queries using SQL in Microsoft Access

Complex for preparing aeronautical information documents Creation of aeronautical data base General provisions The main source of aeronautical information of the complex is the relational database of aeronautical

1. Estimated monitoring tools. Questions submitted to the survey (for discussion) sections of the discipline: Section 1. Introduction to data banks 1. The concept of a data bank (BnD). BnD components. 1.1. Informational

Lecture 2 1 By dictionary: Architectures of information systems Information system is an organizationally ordered set of documents (arrays of documents) and information technologies, including using

J. Bowman, S. Emerson, M. Darnovsky A PRACTICAL GUIDE TO SQL This book will help even when the best user manual is not helpful. Topics that are often overlooked are covered here.

Electronic document management system Arkhivaris Installation manual Moscow, 2009 2 ABSTRACT This document describes the delivery set of the software product “EVFRAT-Document management.