Scribd
Upload
22 views14 pages

Introduction to Basic SQL Commands

This document provides an overview of basic SQL commands and concepts: 1. It describes the history of databases moving from flat files to relational databases with relationships between data. SQL allows users to access and manipulate data in these relational databases. 2. The four basic SQL commands - SELECT, INSERT, UPDATE, DELETE - are introduced for retrieving, adding, modifying and deleting data. SELECT is highlighted as a safe function that will not damage the database. 3. Examples are provided of basic SELECT statements to retrieve data from single and joined tables, including the use of WHERE clauses to filter results. Joins allow combining data from multiple tables to fully answer queries.

Uploaded by

ihatekris2
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
22 views14 pages

Introduction to Basic SQL Commands

This document provides an overview of basic SQL commands and concepts: 1. It describes the history of databases moving from flat files to relational databases with relationships between data. SQL allows users to access and manipulate data in these relational databases. 2. The four basic SQL commands - SELECT, INSERT, UPDATE, DELETE - are introduced for retrieving, adding, modifying and deleting data. SELECT is highlighted as a safe function that will not damage the database. 3. Examples are provided of basic SELECT statements to retrieve data from single and joined tables, including the use of WHERE clauses to filter results. Joins allow combining data from multiple tables to fully answer queries.

Uploaded by

ihatekris2
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

Basic SQL TrainingBasic HistoryDatabases were originally set up as flat files.

These flat files were very similar to


excel documents, where the same data would be repeated over and over again. In
1970, EF Cod deemed this inefficient and decided to create a relational database
system that would create relationships between data in the database.- such as peoples
names, or departments etc.
Relational-> relationships using one piece of data to link it to many tables( this saves
on space and duplication)
Charles River has over 200 tables, each one of our clients has these tables, however,
the data that is in these tables is different, ie different securities etc.
SQL is the language that accesses this data.
The Commands
There are 4 basic commands that need to be understood.
SELECT
INSERT
UPDATE
DELETE

- Retrieves data from the tables


- Puts data into the tables rows, or a new row
- Modifies one or more columns
- Deletes a row or column in the table.

The SELECT function is a safe function that will not allow you to screw up the
database (or DB)

Use Microsoft SQL Enterprise, Query analyser.

This program is often just under your programs, but may be hidden, as is above, under
another directory. Log in, selecting the server required.

If a blank screen comes up, press F8, which will bring up the below screen.

The top left hand highlighted shows the server the database is situated on. The top
highlighted box shows the database that is being looked at, and the bottom box shows
the expandable
Using v723, expand the left hand pane option- ( v723- User tables)

To execute the
statement, hit this button

The user can now see what tables are available, and by expanding the next level down
will show the columns that are in the table.
The basic form of a select function are:
SELECT
FROM
WHERE
The use of the star or *, will bring everything back in that table.
eg, select * from cs_broker, however, structure the query as above, putting the new
query in the next line.
Select *
From cs_broker

The results from this query are then given in the bottom right box, with all columns
and all rows. Also note that there are a total 30 rows.

Try another statement


Select *
From ts_order
This will return all the columns, and rows in the table ts_order.

Command box

Results window

By expanding the tables on the left box, the user can ascertain the columns in each
table.

If the user only required several of the columns that were in the table rather that all of
them, this can be done by specifying each column required. For instance, the user
only requires, order_id, status, security_id , all from the ts_order table.
The statement to retrieve only these would be:
Select order_id, sec_id, status
From ts_order

The columns are returned in no particular order.

To take the statement further, the user may only want to see the above statement with
securities that have a READY status.
Select order_id, sec_id, status
From ts_order
Where status = READY
The use of the where statement is now used to define the columns status. The status
must be within single quotes.

NB- the information within the quotes is note case sensitive, however, for oracle it is.

Joining
Joining tables is required when some columns do not show all of the data is required
for the query that is required.
Perform the following statement:
Select order_id, trans_type, sec_id, status
From ts_order
Where status = READY

Now select the following table;


Select csm_security

Notice that when the query has been run, and that there are two queries in the top box,
there two box appear below. This is sometime useful, however, to receive only one
box, highlight the required statement, and then run.

If the user requires data from two different tables, aliases are required to associate the
two tables. This basically means, using the common data between the two tables, a
relational query is then created.
Select *
From csm_security
Sec_id is a common denominator for these two tables, therefore, it is possible to link
these two tables, using this common factor.

Using the above information, it is then possible to associate the csm_security table to
the ts_order table, by using association.
Associate sec_id(in the ts_order table) to sec_name ( in the csm_security table)
By adding the csm_security to the from statement will create an alias.

Select o.order_id, o.trans_type, o_sec_id, [Link], sec.sec_name


From ts_order o, csm_security sec
Where [Link] = ready
And o.sec_id = sec.sec_id
The from statement creates the associates by adding the o, at the end of each table
where the data should be taken from the ts_order table, and the sec, at the end of the
csm_security table.

This statement has now brought back all the securities that are in the csm_security
table, and ts_order table that have a status of ready, returning their name, and the
trans_type, and order_id, using the order_id as the common denominator.

Important Tables to be aware of:


Ts_order_alloc
cs_position
cs_fund
csm_security

(Contains, order_id, account_id, target_quantity)


( account, sec_id)
(fund number)
( sec_name, currency)

Try this:
Run a query to find all orders in the ready status
Pick one of the orders- find the allocations for that one order.
Now show the account name for each of those allocations
Basic Examples
select*
fromts_order
wheremanager='XBBJNW9'
and
trans_type='SELLL'
and
order_acct_cd='GEMZ'
and
trade_datebetweento_date('01/01/2009','dd/mm/yyyy')andto_date
('29/12/2009','dd/mm/yyyy')

select*
fromcsm_security
wheresec_typ_cd='CURR'

Common questions

Powered by AI

In SQL, data can be retrieved in a specific order using the ORDER BY clause, which sorts results based on specified columns either in ascending or descending order. Formatting can be achieved through functions that format data or by defining the output structure explicitly. Organizing data in a specific order is crucial for analysis and reporting as it highlights trends, facilitates comparisons, and aligns with the user or business requirements. Structured presentation of data ensures usability and clarity in interpretation .

SQL commands like INSERT, UPDATE, and DELETE directly modify database contents by adding, altering, or removing data. INSERT adds new data to a table, UPDATE changes existing data, and DELETE removes specified data. Precautions include backing up the database, ensuring precise WHERE clauses to target only intended records, and executing commands in a controlled environment to prevent data loss or corruption. Misuse or errors in these commands can lead to unintended modifications, significantly impacting data integrity .

A query to filter and display orders based on multiple criteria would use the WHERE clause to combine conditions. The example structure would be: "SELECT * FROM ts_order WHERE manager = 'XBBJNW9' AND trans_type = 'SELLL' AND order_acct_cd = 'GEMZ' AND trade_date BETWEEN to_date('01/01/2009', 'dd/mm/yyyy') AND to_date('29/12/2009', 'dd/mm/yyyy')". This query retrieves orders managed by XBBJNW9, of transaction type SELLL, in the account GEMZ, within the specified date range .

The transition from flat files to relational databases improved data management efficiency by creating relationships between data which eliminated redundancy and minimized data duplication. Flat files, which resembled spreadsheets where the same data might be repeated multiple times, were inefficient. With EF Cod's introduction of relational databases in 1970, data could be linked across multiple tables using key relationships, thereby saving space and facilitating more streamlined data retrieval .

Using aliases in join operations on multiple tables is important because they simplify reference to columns from different tables, especially when table names are long or when the same table is referenced more than once in a query. Aliases improve readability and reduce complexity, making it easier to write and understand the query by associating each table with a short, unique identifier .

SQL plays a critical role in managing relational databases as it provides commands for data retrieval and manipulation. The SELECT command is considered safe because it does not modify the database; it only retrieves data based on specified criteria, ensuring that users do not accidentally alter or corrupt the database while querying information .

Relational databases are particularly advantageous for organizations with numerous and diverse datasets because they allow for efficient data management and retrieval through relational structuring. For financial institutions, this means handling large volumes of transactional data with minimized redundancy and optimized performance. Data from various sources can be linked and accessed without duplication, offering comprehensive insights and facilitating compliance with regulatory requirements by ensuring data consistency and integrity .

SQL can filter and retrieve specific data from a table by using the WHERE clause to specify criteria. For example, to retrieve orders from the 'ts_order' table with a status of 'READY,' the query would be: "SELECT order_id, sec_id, status FROM ts_order WHERE status = 'READY';" This query limits results to rows where the status column matches 'READY' .

Joining the 'ts_order' and 'csm_security' tables using common denominators like 'sec_id' allows for the integration of data from both tables, providing comprehensive results in a single query. This enhances query results by combining associated information from different relational data sources, such as retrieving order details along with their respective securities' names, thus providing a more complete view without the need to separately query and manually combine results from each table .

E.F. Codd introduced the concept of relational database systems in 1970, which revolutionized how data was structured and accessed by creating relationships between data elements. His work laid the foundation for relational data models that eliminate redundancy and optimize storage. Contemporary SQL practices reflect Codd's principles by emphasizing structured queries, normalization, and the use of keys to maintain relational integrity, illustrating his lasting impact on modern database architectures .

You might also like