- Overview of Relational Databases, SQL and PL/pgSGL
- Discuss the Theoretical Aspects of a Relational Database.
- Describe the Postgres Implementation of the RDBMS and ORDBMS.
See documentation on Oracle side (same principle).
It operates under a client-server arquitecture , where the server side is composed of:
- A Database system which consists of a directory structure, database files where data will live, shared catalog tables,etc. (Analoguos to Oracle would be (in the same order): OFA (Oracle Flexible Arcquitecture),Data Files,Data Dictionary), it is within this Database system that every database will reside.
- A process called postmaster which manages connections to the Postgres server process. In contrast to Oracle, postmaster acts like a dispatcher in that it manages connections for incoming clients, and passes them on to a server process.
- Server Process: These server process are the glue between the postmaster process and the Database system , although this relation would be stretched, it is lika an Oracle Instance.
The client side can be composed of any of the following:
- An interactive environment to call SQL/Database commands, Postgres provides an environment called psql (similar to SQL*Plus) which is able to manipulate information in a database from the Database system.
- A driver implmentation (ODBC) designed to communicate and manipulate data in a database, through a Web-Server application or other means.
It should be noted that as every incoming connection is handled by postmaster and passed to a server process, once this is done , the communication is establishd directly between the client and Server Process , the postmaster drops off the communication circle.
PL/pgSQL is a Procedural Language extension to Postgres database. SQL is non-procedural language ,in it the programmer only describes what work to perform, how to perform the work is left to the "Planner/Optimizer", in contrast PL/pgSQL is like any 3GL procedural language, it requires step by step instructions defininig what to do next.
PL/pgSQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL.In Postgres it is also possible to create functions in procedural languages like Perl and Tcl, which interspersed with SQL are called PLPerl and PLTcl respectively.
- List the capabilities of SQL SELECT statements
- Execute a basic SELECT statement
- Differentiate between SQL statements and PSQL (Analogous to Oracle's Sql*Plus) commands.
select * ....[everything]
select distinct ....[distinct values in selection]
select .......for update of ...[Aquires a lock]
select * from users where user_id=3 select email,user_name from employees where name='Daniel'
The difference between SQL and PSQL is very stright forward, any command which begins with an unquoted backslash (
\ ) is considered a PSQL command, these commands are sometimes refered to as:
Meta-commands ,some commands are:
\connect: This commands connects to particular database specified as the argument.
\d: Shows all columns of the specified table,view,index or sequence, the types and special attributes related to it, the
\d+command could also be used which shows any comments associated with the table,view or index.
\df (regexp): This command lists all the functions with arguments and return types that match the
\distvS (regexp): In general the
\dcommand could be followed by a letter
i s t vor
Swhere they respectively mean: index, sequence, table, view and System table,if a
regular expressionis provided it limits the search to that string, you may also add the
+ (plus sign)to indicate that extra comments be displayed.
\dp: Is used to display permissions on the database. a
\e: Escapes the psql environment and opens a text editor in the terminal ,it defaults to the text editor specified by the environment variables:PSQL_EDITOR,EDITOR or VISUAL (in that order).
\echo (text): Echoes the text back to the terminal, normally used when the
\ocommand is activated.
\encoding (type): Sets the current encoding, if no type is specified it displays the currents client (psql) encoding.
\i (file): Reads input from the specified file,this is typically used in conjunction with the
\l: List all the databases in the current database system
\o file: Pipes out all output to a specific file.
\p: Displays current buffer
\pset (parameters): This command along with various parameters determines how the output will displayed on the terminal
NOTE: These are only the basic commands, there are various other shortcuts that are not mentioned, you should refer to the
psql manual page for the complete list.
- Limit the Rows retrieved by a query
- Sort the rows retrieved by a query
select animals from zoo where classification='mammals'; select cars from lot where year > to_date('2000-01-01','YYYY-MM-DD') ; select boat_names from triton where boat_names like 'ki%'; (The
%acts as a wildcard,SQL compliance with Oracle)
select houses from city where city='Ensenada' order by block_id; select cuartos from hoteles order by precio desc; select cuarto from hoteles order by precio asc;[asc is the default, SQL complianance with Oracle]