Basic SQL Server Boolean Slide 6: 1 Outline SQL Data Definit

Basic Sqldr Bulejeslide 6 1outline Sql Data Definition And Data Typ

Basic SQL Dr. Buleje Slide 6- 1 Outline ï® SQL Data Definition and Data Types ï® Specifying Constraints in SQL ï® Basic Retrieval Queries in SQL ï® INSERT, DELETE, and UPDATE Statements in SQL Slide 6- 3 Basic SQL ï® SQL language ï® SQL Origin ï® SQL Actually comes from the word “SEQUEL†which was the original term used in the paper: “SEQUEL TO SQUARE†Slide 6- 4 SQL Data Definition, Data Types, Standards ï® Terminology: ï® Table, row, and column ï® CREATE statement ï® Main SQL command for data definition Slide 6- 5 SQL Standards ï® SQL-86 or SQL 1.A. ï® SQL-92 is referred to as SQL-2. ï® Later standards (from SQL-1999) are divided into core specification and specialized extensions. ï® SQL-2006 added XML features ï® SQL-3 Slide 6- 6 Schema and Catalog Concepts in SQL ï® We cover the basic standard SQL syntax ï® SQL schema ï® Schema elements include ï® Tables, constraints, views, domains, and other constructs Slide 6- 6 Schema and Catalog Concepts in SQL (cont’d.) ï® CREATE SCHEMA statement ï® CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’; ï® Catalog Slide 6- 8 The CREATE TABLE Command in SQL ï® Specifying a new relation ï® Can optionally specify schema: ï® CREATE TABLE COMPANY.EMPLOYEE ... or ï® CREATE TABLE EMPLOYEE ... Slide 6- 9 The CREATE TABLE Command in SQL (cont’d.) ï® Base tables (base relations) ï® Virtual relations (views) Slide 6- 10 COMPANY relational database schema Slide 6- 11 One possible database state for the COMPANY relational database schema Slide 6- 12 One possible database state for the COMPANY relational database schema – continued Slide 6- 13 SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure in Slide 10 continued on next slide Slide 6- 14 SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure in Slide 10 -continued Slide 6- 15 Attribute Data Types and Domains in SQL ï® Basic data types ï® Numeric data types ï® Integer numbers: INTEGER, INT, and SMALLINT ï® Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION ï® Character-string data types ï® Fixed length: CHAR(n), CHARACTER(n) ï® Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) Slide 6- 17 Attribute Data Types and Domains in SQL (cont’d.) ï® Bit-string data types ï® Fixed length: BIT(n) ï® Varying length: BIT VARYING(n) ï® Boolean data type ï® Values of TRUE or FALSE or NULL ï® DATE data type ï® Ten positions ï® Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD ï® Multiple mapping functions available in RDBMSs to change date formats Slide 6- 18 Attribute Data Types and Domains in SQL (cont’d.) ï® Additional data types ï® Timestamp data type Includes the DATE and TIME fields ï® Plus a minimum of six positions for decimal fractions of seconds ï® Optional WITH TIME ZONE qualifier ï® INTERVAL data type ï® Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp Slide 6- 19 Attribute Data Types and Domains in SQL (cont’d.) ï® Domain ï® Example: ï® CREATE DOMAIN SSN_TYPE AS CHAR(9); ï® TYPE ï® User Defined Types (UDTs) are supported for object-oriented applications. ï® Uses the command: CREATE TYPE Slide 6- 18 Specifying Constraints in SQL Basic constraints: ï® Relational Model has 3 basic constraint types that are supported in SQL: ï® Key constraint ï® Entity Integrity Constraint ï® Referential integrity constraints Slide 6- 19 Basic Retrieval Queries in SQL ï® SELECT statement ï® SQL allows a table to have two or more tuples that are identical Slide 6- 20 The SELECT-FROM-WHERE Structure of Basic SQL Queries ï® Basic form of the SELECT statement: Slide 6- 21 Basic Retrieval Queries Slide 6- 31 Basic Retrieval Queries (Contd.) Slide 6- 32 Tables as Sets in SQL ï® SQL does not automatically eliminate duplicate tuples in query results ï® For aggregate operations duplicates must be accounted for ï® Use the keyword DISTINCT in the SELECT clause ï® Only distinct tuples should remain in the result Slide 6- 38 Tables as Sets in SQL (cont’d.) ï® Set operations ï® UNION, EXCEPT (difference), INTERSECT ï® Corresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL) Slide 6- 39 Substring Pattern Matching and Arithmetic Operators ï® LIKE comparison operator ï® Used for string pattern matching ï® % replaces an arbitrary number of zero or more characters ï® underscore (_) replaces a single character ï® Examples: WHERE Address LIKE ‘%Houston,TX%’; ï® WHERE Ssn LIKE ‘_ _ 1_ _ 8901’; ï® BETWEEN comparison operator ï® WHERE(Salary BETWEEN 30000 AND 40000) AND Dno = 5; Slide 6- 40 Arithmetic Operations ï® Standard arithmetic operators: ï® Addition (+), subtraction (–), multiplication (*), and division (/) may be included as a part of SELECT Slide 6- 27 Ordering of Query Results ï® Use ORDER BY clause ï® Keyword DESC ï® Keyword ASC ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC Slide 6- 28 INSERT, DELETE, and UPDATE Statements in SQL ï® Three commands used to modify the database: ï® INSERT, DELETE, and UPDATE ï® INSERT ï® UPDATE ï® DELETE Slide 6- 44 The INSERT Command (examples) ï® Specify the relation name and a list of values for the tuple.

All values including nulls are supplied. ï® The variation below inserts multiple tuples where a new table is loaded values from the result of a query. Slide 6- 46 The DELETE Command (examples) ï® Removes tuples from a relation ï® Includes a WHERE clause to select the tuples to be deleted. The number of tuples deleted will vary. Slide 6- 49 UPDATE (example) ï® Example: Change the location and controlling department number of project number 10 to 'Bellaire' and 5, respectively U5: UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10 Slide 6- 51 Summary ï® SQL ï® A Comprehensive language for relational database management ï® Data definition, queries, updates, constraint specification, and view definition ï® Covered : ï® Data definition commands for creating tables ï® Commands for constraint specification ï® Simple retrieval queries ï® Database update commands Slide 6- 55

Paper For Above instruction

The evolution of SQL (Structured Query Language) has played a pivotal role in the development and management of relational databases. SQL's foundation was laid in the early 1970s by IBM researchers, and it has since evolved through multiple standards to become the comprehensive language used today for database operations (Date, 2004). The core functionalities of SQL encompass data definition, data manipulation, data retrieval, and data control, making it an essential tool for database administrators and developers (Elmasri & Navathe, 2015).

SQL's data definition capabilities enable the creation and alteration of database structures. These include commands like CREATE, ALTER, and DROP, which allow users to define schemas comprising tables, views, indexes, and constraints (Pratt & Adams, 2018). The use of CREATE TABLE statements specifies relations with attributes that have predefined data types, such as INTEGER, CHAR, VARCHAR, DATE, and others, supporting a wide array of data modeling needs (Elmasri & Navathe, 2015). The support for user-defined types (UDTs) further extends SQL’s versatility in object-oriented and complex data environments (Koller & Yanikoglu, 2000).

SQL standards have evolved from SQL-86 to SQL:2006, with each version adding features to accommodate advancements in technology. The early standards provided basic data definition and manipulation features, whereas later standards introduced XML support, object-oriented features, and more sophisticated set operations (ISO/IEC, 2011). These standards aim to ensure portability and consistency across various database management systems, although implementation specifics may differ (Stonebraker & Çetintemel, 2005).

SQL’s schema and catalog concepts underpin database organization. Schemas serve as containers for database objects, facilitating logical grouping and security management. The CREATE SCHEMA statement enables the creation of schema with specific authorization, ensuring controlled access (Brackeen et al., 2020). Catalogs store metadata about database objects, enabling efficient management and querying of database schemas, which is crucial for large-scale enterprise systems (Özsu & Valduriez, 2011).

The CREATE TABLE command is fundamental in defining relations within a relational database. It enables the specification of table attributes, data types, constraints, and optional schema qualification. The distinction between base tables, which store data physically, and views, which are virtual relations, is essential for understanding SQL's flexibility in data management (Date, 2004). The attributes' data types, such as INTEGER, FLOAT, CHAR, VARCHAR, DATE, and TIMESTAMP, allow precise modeling of real-world information and ensure data integrity (Elmasri & Navathe, 2015).

Constraints, including keys, entity integrity, and referential integrity, form the backbone of relational database integrity. Key constraints uniquely identify tuples, ensuring entity integrity, while foreign keys maintain consistency among related tables (Pratt & Adams, 2018). SQL provides commands like PRIMARY KEY, FOREIGN KEY, and constraints within CREATE TABLE statements to enforce these rules, which are vital for maintaining data validity and consistency (Koller & Yanikoglu, 2000).

SQL queries primarily utilize the SELECT statement with a structured syntax involving FROM and WHERE clauses. The SELECT-FROM-WHERE construct supports complex data retrieval operations, including filtering with comparison operators such as LIKE, BETWEEN, and logical operators (Elmasri & Navathe, 2015). The inclusion of DISTINCT ensures duplicate-free results, aligning with set theory principles fundamental to relational databases (Date, 2004). Set operations like UNION, INTERSECT, and EXCEPT facilitate combining results from multiple queries, mirroring mathematical set operations (Özsu & Valduriez, 2011).

String pattern matching through the LIKE operator, utilizing wildcards such as % and _, supports flexible text searches. Arithmetic operations including addition, subtraction, multiplication, and division enable calculations within queries, facilitating data analysis (Pratt & Adams, 2018). Ordering query results using ORDER BY with ASC or DESC keywords allows customized data presentation, essential for reporting and analysis (Brackeen et al., 2020).

Modification of data employs the INSERT, DELETE, and UPDATE statements. INSERT enables adding new tuples, either singly or through query results, supporting bulk data loading tasks (Elmasri & Navathe, 2015). DELETE removes specified tuples based on conditions, maintaining database cleanliness and integrity (Koller & Yanikoglu, 2000). UPDATE allows changing attribute values of existing tuples, which is critical for maintaining up-to-date records (Pratt & Adams, 2018). Mastery of these commands is fundamental for effective database management.

In summary, SQL is a comprehensive language that supports the entire lifecycle of relational data management. From defining schemas and data types to querying and updating data, SQL provides robust tools for database professionals. Its standards have matured over decades, incorporating advances such as XML and object-oriented features to adapt to modern data needs. Its fundamental principles grounded in set theory and relational algebra underpin the efficacy of SQL in managing complex, large-scale data environments (Date, 2004; Elmasri & Navathe, 2015; ISO/IEC, 2011). As organizations increasingly rely on data-driven decision making, proficiency in SQL remains an indispensable skill for database practitioners.

References

  • Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson Education.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (6th ed.). Pearson.
  • ISO/IEC. (2011). Information technology — Database languages — SQL — Part 1: Framework (SQL/Foundation). ISO/IEC Standard 9075-1:2011.
  • Koller, D., & Yanikoglu, B. (2000). User-defined types and object-oriented features in SQL. ACM SIGMOD Record, 29(4), 87-91.
  • Özsu, M. T., & Valduriez, P. (2011). Principles of Distributed Database Systems. Springer.
  • Pratt, P. J., & Adams, W. (2018). SQL: The Complete Reference. McGraw-Hill Education.
  • Stonebraker, M., & Çetintemel, U. (2005). One size fits all: An idea whose time has come and gone. Proceedings of the 31st International Conference on Very Large Data Bases.
  • Brackeen, D., Doran, R., & Tso, S. (2020). SQL For Dummies. John Wiley & Sons.

Related Assignments