Substring pattern matching and arithmetic operators

Info icon This preview shows pages 37–46. Sign up to view the full content.

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 Standard arithmetic operators: Addition (+), subtraction (–), multiplication (*), and division (/) BETWEEN comparison operator Database Design -- Basic SQL 37
Image of page 37

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Arithmetic Operators Standard arithmetic operators: Addition (+), subtraction (–), multiplication (*), and division (/) may be included as a part of SELECT Query 13. Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise. SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND P.Pname=‘ProductX’; Database Design -- Basic SQL 38
Image of page 38
Ordering of Query Results SQL results are inherently not ordered. To change this, use ORDER BY clause Keyword DESC to see result in a descending order of values Keyword ASC to specify ascending order explicitly ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC Database Design -- Basic SQL 39
Image of page 39

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Basic SQL Retrieval Query Database Design -- Basic SQL 40
Image of page 40
Exists vs. In IN does a direct match between the column specified before the IN keyword and the values returned by the subquery. When using IN there can only be a single column specified in the select clause of the subquery Exists doesn’t check for a match, it doesn’t care in the slightest what values have been returned from the expression, it just checks for whether a row exists or not. Because of that, if there’s no predicate in the WHERE clause of the subquery that compares rows in the subquery with rows in the outer query, EXISTS will either return true for all the rows in the outer query or it will return false for all the rows in the outer query Exists is better for when comparisons are needed on two or more columns. This cannot be done easily with an IN Database Design -- Basic SQL 41
Image of page 41

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Modifying the Database Data Three commands used to modify the database: INSERT , DELETE , and UPDATE INSERT typically inserts a tuple (row) in a relation (table) UPDATE may update a number of tuples (rows) in a relation (table) that satisfy the condition DELETE may also update a number of tuples (rows) in a relation (table) that satisfy the condition Database Design -- Basic SQL 42
Image of page 42
The INSERT Command Specify the relation name and a list of values for the tuple Database Design -- Basic SQL 43
Image of page 43

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

INSERT Statement INSERT INTO Employee (fname, minit, lname, ssn, bdate, dno) values(‘Richard’, ‘S’, ‘Anderson’, ‘999333111’, ‘9/14/1980’, 4) Attribute values should be listed in the same order as the attributes were specified in the CREATE TABLE command Constraints on data types are observed automatically Any integrity constraints as a part of the DDL specification are enforced Database Design -- Basic SQL 44
Image of page 44
Bulk Loading of Tables Another variation of INSERT is used for bulk-loading of several tuples into tables
Image of page 45

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 46
This is the end of the preview. Sign up to access the rest of the document.
  • Fall '09
  • SUNANHAN
  • Relational model

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern