Tiêu đề Structure Query Language (SQL).pdf ✅

4.0 Structure Query Language (SQL) © T. Paneru 2 The DDL allow to enforce constrains in the database. For example: student_id should begin with `S’, address could not be null etc. CREATE TABLE STUDENT ( student_id VARCHAR2 (3), address VARCHAR2 NOT NULL, CONSTRAINT ch_student_id CHECK (student_id LIKE `S%’) ); The database systems check these constraints every time the database is updated. Data Manipulation Language: • A data manipulation language is a language that enables users to access or manipulate the data in database. The data manipulation means : o Retrieval of information stored in database. o The insertion of new information into database. o Deletion of information from database. o Modification of data in database. Two types of data manipulation languages are: • Procedural DML: User need to specify what data are needed to retrieve (modify) and how to retrieve those data. • Non Procedural (Declarative DML) : User requires to specify what data are needed to retrieve without specifying how to get (retrieve) those data. o Non procedural DML are easier to understand and use than procedural DML, since user does not have to specify now to get data from database. o The DML component of SQL is non procedural language. Example: Consider a simple relational database. The customer table The account table The depositor table
4.0 Structure Query Language (SQL) © T. Paneru 3 Some queries and their equivalent SQL statement Query: Find the name of customer whose customer_id C001. SELECT customer. customer_name FROM customer WHERE customer.customer_id = ` C001’; OR SELECT customer_name FROM customer WHERE customer_id = ` C001’; Note: We don’t need to specify the table name while referencing column_name if we are taking column from only one table. Query: Find the name and balance of the customer. SELECT customer.customer_name,account.balance FROM customer,account.balance WHERE customer.customer_id= depositor.customer_id AND depositor.acount_no = account.account_no; Problem : Insert record to customer table. customer_id : C005 customer_name : MICHAEL address : KATHMANDU INSERT INTO customer (customer_id, customer_name, address) VALUES (`C005’ , `MICHAEL’ , `KATHMANDU’) ; OR INSERT INTO Customer values (`C005’ , `MICHAEL’ , `KATHMANDU’); Note: Column name need not to specify if we are going to insert values for all columns of table. Query: Delete record from depositor whose customer_id is `C004’. : DELETE FROM depositor WHERE Customer_id = `C004’; What happen if we execute DELETE statement as below? DELETE FROM depositor; • Deletes all records from the table `depositor’ Problem: If you attempt to delete all records of customer from customer table, what happen ? • You cannot delete all records, only when “account” and “deposit” tables are empty or only when these table contains records that are not related to the customer. Query: Increase the balance by 5% in account table whose account no is `A101’ or current balance is only 200. UPDATE account SET balance = balance + (balance + 0.05) WHERE account_no = `A1001’ OR balance = 200;
4.0 Structure Query Language (SQL) © T. Paneru 4 Note: Sometimes database languages are also categorized with Data Control Language. That is 1. Data Definition Language (DDL) 2. Data Control Language (DCL) 3. Data Manipulation (DML) Data Control language is a language that controls the behavior of database. In SQL, COMMIT, ROLLBACK, commands go under Data Control Language. • COMMIT: Saves the changes made to database. • ROLLBACK: Undo changes to database from the current state database to last commit state. Question: Why we need query language, even we have formal languages? (formal languages, relational algebra, relational calculus ) • The formal languages provides concise notation for representing query. But commercial database system requires more user friendly query language. This is a main reason for why we need query languages, even we have formal languages. • The formal languages form the basis for data manipulation language of DBMS only but DBMS/commercial DBMS also supports data definition capabilities as well as data manipulation capabilities. • SQL is a most popular and powerful query language. It can do much more than just query database. It ca define structure of data, modify data in database and allow to specify security constraints. • SQL is a truly non procedural language. It has all features of relational algebra, relational calculus as well as its own powerful features. 4.3 Different parts of SQL Language 1. Data Definition Language (DDL): SQL DDL provides commands for defining relation schemas, deleting schema, deleting relations and modifying relational schemas. Example: CREATE, ALTER, DROP CREATE TABLE dept ( dept no NUMBER(2) PRIMARY KEY, dname VARCHAR2(20) NOT NULL ); CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, deptno NUMBER(3), ename VARCHAR2(10) NOT NULL, sal NUMBER(5) NOT NULL, CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept