Home > Articles > Data > SQL

An Introduction to SQL

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Building a Database

In this section, you will learn to build simple PostgreSQL databases. PostgreSQL supports a lot of functions and features to make the definition of a data structure easy. In addition, data can easily be inserted into tables using simple SQL commands.

Building Simple Data Structures

The core component of every data structure is a table. Tables are used to store the data, and most database operations are based on tables. Defining and removing tables correctly are essential steps when working with databases.

Creating Tables

In this section, you learn how to create tables with the help of SQL commands. If you want to create a table, you can use the CREATE TABLE command.

If you want to create a table called emp for storing names and salaries, you use the following command:

name=# CREATE TABLE emp(id serial, empname varchar(50), sal numeric(9,2));

The display is the following:

NOTICE: CREATE TABLE will create implicit sequence 'emp_id_seq' for SERIAL column 'emp.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'emp_id_key' for table 'emp'
CREATE

You can check to see whether the table has been created by using the \d command—in this case, \d emp:

name=# \d emp
Table "emp"

 Attribute |  Type   |         Modifier
-----------+--------------+------------------------------------- --------
 id    | integer   | not null default nextval('emp_id_seq'::text)
 empname  | varchar(50) |
 sal    | numeric(9,2) |
Index: emp_id_key

The table has successfully been created and contains three columns. The first column is used as a sequence; every record will have a unique id. If the datatype serial is used for a column, a sequence will implicitly be created by the database. The second column will be used to store the name of the person. Because a name has no fixed length, you use the datatype varchar() with a maximum length of 50 characters. The third column will be used to store the salary. Salaries are usually decimal values. In this case, the salary can have up to 7 digits before the comma; 2 digits can be used after the comma. PostgreSQL automatically creates an index on the first column if no special primary key is defined.

Here is a second, slightly different CREATE TABLE command:

CREATE TABLE emp2 (id serial, 
    empname varchar(50) UNIQUE, 
    sal numeric(9,2) NOT NULL, 
    currency varchar(4) DEFAULT 'USD');

The following is displayed:

NOTICE: CREATE TABLE will create implicit sequence 'emp2_id_seq' for SERIAL column 'emp2.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'emp2_id_key' for table 'emp2'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'emp2_empname_key' for table 'emp2'
CREATE

We have created a table called emp2, but the column named empname can be used to store only unique names. This can be useful because it is extremely confusing to have two people with exactly the same name but with different salaries in the database. If someone tries to insert a name twice, the database will report an error. NOT NULL means that a correct salary has to be available. If no salary for a particular row is inserted into the table, an error will be displayed. The fourth column is used to store the currency of the salary—the default value is set to USD. The default value will be used if no value is inserted into the field.

If you don't know the syntax of the command by heart, you can simply use the \h CREATE TABLE command. The following lines will be displayed:

name=# \h CREATE TABLE 
Command: CREATE TABLE 
Description: Creates a new table 

Syntax: CREATE [ TEMPORARY | TEMP ] TABLE table ( column type [ NULL | NOT NULL ] [ UNIQUE ] 
[ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ] ] [, ... ] 
[, PRIMARY KEY ( column [, ...] ) ] [, CHECK ( condition ) ] [, table_constraint_clause ] ) 
[ INHERITS ( inherited_table [, ...] ) ]

Using Temporary Tables

Temporary tables are an extremely powerful feature of PostgreSQL. They can be used to store session-specific information in a comfortable way. Temporary tables can be created with the CREATE TEMPORARY TABLE command, which works like the CREATE TABLE command.

The difference between ordinary and temporary tables is that temporary tables are visible only while the session is active. If users quit the session, all temporary tables they have created will be deleted by the database.

If two users log in simultaneously, both users can create temporary tables with the same name because the temporary tables of one user can't be seen by the other user. This feature makes temporary tables easy and safe to use.

The DROP TABLE Command

The DROP TABLE command can be used to delete tables. Here is an example:

DROP TABLE emp2;

The table will be dropped immediately and no ROLLBACK can be performed. You will learn about ROLLBACK and transactions in Chapter 4, "PL/PGSQL."

Be careful when using DROP TABLE. DROP TABLE can also be used to drop multiple tables at once. If you want to drop more than one table, add a comma; the name of the table you want to drop to the statement and two tables will be deleted (as long as you have the permission and the tables are available).

DROP TABLE does not automatically drop sequences that are implicitly created when you use serials in a table. This is extremely important when you want to create a table with the same name and structure you have just dropped, because the sequence won't be overwritten. For solving the problem, use the DROP SEQUENCE command manually.

The ALTER TABLE Command

The ALTER TABLE command can be used to perform multiple operations. ALTER TABLE can be used to add columns to a table. The following example adds a column called currency to table emp (the one we created in the CREATE TABLE section):

ALTER TABLE emp ADD COLUMN currency varchar(4);

The new column is a varchar and can be up to 4 characters long. If we want to set the default value of that column to USD, we have to write a second SQL statement:

ALTER TABLE emp ALTER COLUMN currency SET DEFAULT 'USD';

The default value is set to USD, but ALTER TABLE can also be used to rename tables. Here is an example where the table emp is renamed to oldemp:

ALTER TABLE emp RENAME TO oldemp;

Columns can also be renamed:

ALTER TABLE emp RENAME COLUMN sal TO salary;

In the example, the column sal is renamed to salary. The ALTER TABLE command is indeed powerful and useful. Here is the complete definition of the command's syntax (try \h ALTER TABLE):

Command:   ALTER TABLE
Description: Modifies table properties
Syntax:
ALTER TABLE table [ * ]
  ADD [ COLUMN ] column type
ALTER TABLE table [ * ]
  ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
  RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
  RENAME TO newtable
ALTER TABLE table
  ADD table constraint definition

You can see that setting or dropping default values and adding constraints can also be done easily.

Note

Renaming tables can sometimes be very tricky and can lead to confusion. We have renamed the table emp to oldemp, but what about the sequence used in the first column? It is still called emp_id_seq. If the number of tables and sequences increase, this can become a complicated and confusing issue.

Creating and Dropping Indices

Indices are used to speed up queries. PostgreSQL normally uses B-trees for indexing a column. It is also possible to create a single index for multiple columns, but let's look at the syntax of CREATE INDEX first:

persons=# \h CREATE INDEX
Command:   CREATE INDEX
Description: Constructs a secondary index
Syntax:
CREATE [ UNIQUE ] INDEX index_name ON table
  [ USING acc_name ] ( column [ ops_name ] [, ...] )
CREATE [ UNIQUE ] INDEX index_name ON table 
  [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )

Let's create an index:

CREATE UNIQUE INDEX idx_oldemp_empname ON oldemp (empname);

In this example, we create an index called idx_old_empname on table oldemp. The index is used to store unique values, which means that a name can appear only once in the table. The index is defined for the column empname. PostgreSQL uses B-trees for indices by default. If you want to use R-trees or hashes, the SQL command has to be modified slightly:

CREATE INDEX idx_oldemp_empname ON oldemp USING HASH (empname);

Note

Hashes and R-trees cannot be created on tables in combination with the UNIQUE constraint. It is also not possible to use T-trees for certain datatypes. If you try to, the following error messages is displayed:

name=# CREATE UNIQUE INDEX idx_oldemp_empname ON oldemp USING HASH (empname);

ERROR: DefineIndex: unique indices are only available with the btree access method

name=# CREATE UNIQUE INDEX idx_oldemp_empname ON oldemp USING RTREE (empname);

ERROR: DefineIndex: unique indices are only available with the btree access method

name=# CREATE INDEX idx_oldemp_empname ON oldemp USING RTREE (empname);

ERROR: DefineIndex: opclass "varchar_ops" not supported by access method "rtree"

Indices can also be defined for multiple columns; here is an example where one index is used for two columns:

CREATE INDEX idx_oldemp_salcur ON oldemp (salary,currency);

If you want to drop the index, use the following command:

DROP INDEX idx_oldemp_salcur;

You should use idx as a prefix or postfix for the name of your index (it helps you execute larger projects).

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020