Scribd
Upload
14 views20 pages

Chapter 6 Structured Query Language

Structured Query Language (SQL) is the standard language for managing relational databases, allowing users to create, manipulate, and control data. SQL commands are categorized into Data Definition Language (DDL), Data Manipulation Language (DML), and Transaction Control Language (TCL), each serving different functions such as defining database structures or manipulating data. The document also outlines how to run SQL queries, create tables, insert data, and perform simple queries using various SQL commands.

Uploaded by

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

Chapter 6 Structured Query Language

Structured Query Language (SQL) is the standard language for managing relational databases, allowing users to create, manipulate, and control data. SQL commands are categorized into Data Definition Language (DDL), Data Manipulation Language (DML), and Transaction Control Language (TCL), each serving different functions such as defining database structures or manipulating data. The document also outlines how to run SQL queries, create tables, insert data, and perform simple queries using various SQL commands.

Uploaded by

abhishekduke1611
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
6 SS SS°tiomn Structured Query Language A SQL Classification f A Running SQL Queries in Base A Creating Tables in MySQL \ A Inserting Data into Table | JA Making Simple Queries Through N Sea Introduction ee | GOL (pronounced “ess-que-el” or “see-quel”) stands for Structured Query Language. SQL is ved to communicate with a relational database. According to ANSI (American National Standards Institute), itis the standard language for relational database management systems. SOL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. SQL Classification a language that enables you to create and operate on The Structured Query Language (SQL) is nae Jated information stored in tables. relational databases, which are sets of rel of SQL Statements 4 i . SQL comman SQL provides many different types of commands used for different purposes sQl can be mainly divided into following categories : * () Data Definition Language (DDL) Commands. Commands that allow you to perform tasks related to data definition €8 © creating, altering and dropping: © granting and revoking privileges ® maintenance commands to 4s, Commands that allow you (i) Data Manipulation Language (DML) Comic * cetion and modification of data perform data manipulation &$: retrieval, inse stored in a database. and roles. ; 4 si RUE TEOMA GY (iii) Transaction Control Language (SCL) Commands, Commands that allow yen, manage and control the transactions (a transaction is ome complete Unit f oy, involving many steps), ez, © making changes to database, permanent © undoing changes to database, permanent © creating, savepoints © veAting properties for current transactions ‘There are other categories of SOL commands also but above three categories of commands are mainly used by learners, TE Running SOL Queries in Base Open Office Base, like other relational databases, supports SOL. In order to execute an SOX, command in Base, you need to do the following, : 1. Click Tools —» SQL command. 2. Type your SQL command in the Command to execute box in the dialog that appears. 3. Click Execute button below the command box. (see below) Creating Tables Using SQL ‘Tables are defined with the CREATE TABLE command, a DDL command. When a table is created, its columns are named, data types and sizes are supplied for each column. Each table must have at least one column. The syntax of CREATE TABLE command is : CREATE TABLE (column name> { («size>)], [ ( ) ... ]) gate an employee table whose schema is as follows : 7 ecode. ename, sex, grade, grass) nand will be ent? Column names followed command. by their datatypes Rai anes x gate char), goss decimal ) wn (6.7) talks about CREATE TABLE command in details. But before that let us data into table and make simple queries. used data types for defining columns are = ‘Stores integer values in the range of -2147483648 to 2147483647 Stores real or decimal values with exact precision. Stores fixedength strings with a maximum size of 255 characters. Stores variable-length strings with a maximum size of 65,535 characters. Stores date values in the YYY-MM-DD format. Stores combined date/time values in the YYYY-MM-DD HH:MM:SS format. [EI issering Date into Table ws (tuples) are added to relations using INSERT command a DML command of SQL Ins simplest frm, INSERT tks the VALUES (1002, ‘Ravi, ‘M, Ee’, 4670.00) S* the order of values matches the order of columns in the CREATE TABLE Sovee. The same can be done with an alternate command as shown below > "car mr coe aa % ALUES (1001, ‘Raw, , B, 4670.00) a 7 ‘e INSERT statement adds a new row to employee giving a value for “fw. Note that the data values are in the same onder as the column G28 san be added only to some columns in a row by spesving the ae INFORMATION TECHNOLOgy iy For instance, if you want to insert only ecode, ename and sex columns, you use the command INSERT INTO employee (ecode, ename, sex) VALUES (2014, ‘Manju’, F) The columns that are not listed in the INSERT command will have their default value, if it is defined for them, otherwise, NULL value. an INSERT stateme; hose columns. ave either default value defined hey allow NULL values, onl If any other column (that does not have a default value and is defined NOT NULL) is skipped or omitted, an error message is generated and the row is not added. Inserting NULL values To insert value NULL in a specific column, you can type NULL without quotes and NULL \will be inserted in that column. Consider the following, statement : INSERT INTO EMPL (Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (8100, “YASH, ‘ANALYST’, NULL, ‘10-MAY-03', 6000, NULL, 20) See, for Mgr and Comm columns, NULL values have been inserted. Inserting Dates Dates are by default entered in ‘YYYY-MM-DD’ format ic, first four digits depicting year, followed by a hyphen, followed by 2 digits of month, followed by a hyphen and a two digit day. All this is enclosed in single quotes. [ZA Making Simple Queries Through Select Command The SELECT statement (a DML command)is used to pull information from a table. The general form of the statement is : ae eee outa FROM which_table SELECT is a DML WHERE conditions_to_satisfy command. 6.6.1 Selecting All Data The simplest form of SELECT retrieves everything from a table. You just need to specify asterisk in the select-list(what_to_select), ¢.8:, SELECT * FROM pet 6.6.2. Selecting Particular Rows You can select particular rows from a table by 5) clause of the SELECT statement, ¢.g., 1, Select all pets with gender(sex) as male(“m”). SELECT * FROM pet WHERE sex = ‘mm’ 2, Select all pets that were born on or after Jan 1, 2019. ‘SELECT * FROM pet WHERE birth >= ‘2019-1-1' pecify filtering condition through WHERE ate in yyyyemmedd format 1RUCTURED QUERY LANGUAGE 215 5, Select all female-dogs. SELECT * FROM pet WHERE species ~ dog’ AND sex =f 4, Select all snakes or birds. SELECT» FROM pet WHERE species = snake’ OR species = bird’ 5, Select all male cats. SELECT * FROM pet WHERE (species = cat” AND sex = ‘n’) ting Particular Columns You can select particular columns by speclivng coldmmcnimes (2) gttdbutS) nth select-list of the SELECT command, e., a tee, ) ie 1, Display names and birth-dates of all pets, SELECT name, birth FROM pet 2. Display owners of pets born after Feb 2020. ‘SELECT owner FROM pet WHERE birth >'2020-02-01. <————— date in Yyyy-mm-dd format 4 Eliminating Redundant Data (with Keyword DISTINCT) By default, data is selected from all the rows of the table, result gets duplicated. The DISTINCT keyword eliminates SELECT statement. For example, even if the data appearing in the duplicate rows from the results of a 1, Display names of all pet-owners (non-redundant). SELECT DISTINCT owner FROM pet 2. Display distinct species of pets from table pet. SELECT DISTINCT (species) FROM pet. Selecting From All the Rows — ALL Keyword '*in place of keyword DISTINCT, you give keyword ALL then the result retains the duplicate output rows. It is just the same as when you specify neither DISTINCT nor ALL ; ALL is essentially a clarifier rather than a functional argument. Thus if you give SELECT ALL city FROM suppliers ‘twill give values of city column from every row of the table without considering the duplicate entries, Viewing Structure of a Table Ifyou want to know the structure of a table, you can use Describe or Desc command as per following syntax DESCRIBE | DESC For instance, the commands ; DESCRIBE pet or DESC pet will display the structure of table pet. DESC pet. UNTO WCNC 216 6 oO a 67 @ INFORMATION TECHNOLOgy Performing Simple Calculations To perform simple calculations, you can write the expression/formula to be calculated nex keyword SELECT, eg, 1. To calculate 3.14159°6*6 SELECT 3.14159°6*6 2. To obtain current system date SELECT curdate( ) Using Column Aliases The columns that you select in a query can be given a different name ie., column alias name for output purposes. As per following syntax : Select AS (columnalias] [, AS {columnalias]] From For example, SELECT date, type AS “Event Type” FROM event Condition Based on a Range The BETWEEN operator defines a range of values that the column values must fall in to make the condition true. The range includes both lower value and the upper value. For example, to list the items whose QOH falls between 30 to 50 (both inclusive), the command would be : SELECT icode, descp, QOH FROM items WHERE QOH BETWEEN 30 AND 50 6.6.10 Condition Based on a List 6.6.11 To specify a list of values, IN operator is used. The IN operator selects values that match any value in a given list of values. For example, to display a list of members from ‘DELHI’, ‘MUMBAI, ‘CHENNAI or ‘BANGALORE cities, you may give SELECT * FROM members WHERE city IN ‘DELHI’, MUMBAT,, ‘CHENNAI’, ‘BANGALORE’) The NOT IN operator finds rows that do not match in the list. So if you write SELECT * FROM members WHERE city NOT IN (‘DELHT’, ‘MUMBAI’, ‘CHENNAT’) it will list members not from the cities mentioned in the list. Condition Based on Pattern Matches SQL. also includes a string-matching operator, LIKE, for comparisons on character strings using patterns. Patterns are described using two special wildcard characters : © percent (%). The % character matches any substring. underscore (_). The _ character matches any character. The LIKE keyword is used to select rows containing columns that match a wildcard pattern.

You might also like