Kategori: Basis Data

SQL allows the nesting of one query inside another, but only in the WHERE and the HAVING clauses. In addition, SQL permits a subquery only on the right hand side of an operator. Example 1 Find the names and IDs of all faculty members who teach a class in room 'H221'. You could do this with 2 queries as follows: SELECT FACID FROM CLASS WHERE ROOM = 'H221'; ---> RESULT: F101, F102 SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (F101, F102); or you could combine the 2 into a nested query:... Readmore

A JOIN operation is performed when more than one table is specified in the FROM clause. You would join two tables if you need information from both. You must specify the JOIN condition explicitly in SQL. This includes naming the columns in common and the comparison operator. Example 1 Find the name and courses that each faculty member teaches. SELECT FACULTY.FACNAME, COURSENUM FROM FACULTY, CLASS WHERE FACULTY.FACID = CLASS.FACID; FACULTY.FACNAME COURSENUM Adams ART103A Tanaka CIS201A Byrne... Readmore

The ORDER BY clause is used to force the query result to be sorted based on one or more column values. You can select either ascending or descending sort for each named column. Example 1 List the names and IDs of all faculty members arranged in alphabetical order. SELECT FACID, FACNAME FROM FACULTY ORDER BY FACNAME; FACID FACNAME F101 Adams F110 Byrne F221 Smith F202 Smith F105 Tanaka Example 2 List names and IDs of faculty members. The primary sort is the name and the secondary sort is by... Readmore

Aggregate functions allow you to calculate values based upon all data in an attribute of a table. The SQL aggregate functions are: Max, Min, Avg, Sum, Count, StdDev, Variance. Note that AVG and SUM work only with numeric values and both exclude NULL values from the calculations. Example 1 How many students are there? SELECT COUNT(*) FROM STUDENT; COUNT(*) 6 NOTE: COUNT can be used in two ways. COUNT(*) is used to count the number of tuples that satisfy a query. COUNT with DISTINCT is used to... Readmore

The WHERE clause can be enhanced to be more selective. Operators that can appear in WHERE conditions include: =, <>,<,>,>=,<= IN BETWEEN...AND... LIKE IS NULL AND, OR, NOT Example 1 Find the student ID of all math majors with more than 30 credit hours. SELECT STUID FROM STUDENT WHERE MAJOR = 'Math' AND CREDITS > 30; STUID S1015 S1002 Example 2 Find the student ID and last name of students with between 30 and 60 hours (inclusive). SELECT STUID, LNAME FROM STUDENT WHERE... Readmore

Example 1 Retrieve all information about students ('*' means all attributes) SELECT * FROM STUDENT; STUID LNAME FNAME MAJOR CREDITS S1001 Smith Tom History 90 S1010 Burns Edward Art 63 S1015 Jones Mary Math 42 S1002 Chin Ann Math 36 S1020 Rivera Jane CIS 15 S1013 McCarchy Owen Math 9 Example 2 Find the last name, ID, and credits of all students SELECT LNAME, STUID, CREDITS FROM STUDENT; LNAME STUID CREDITS Smith S1001 90 Burns S1010 63 Jones S1015 42 Chin S1002 36 Rivera S1020 15 McCarthy S1013... Readmore

The DML component of SQL is the part that is used to query and update the tables (once they are built via DDL commands or other means). By far, the most commonly used DML statement is the SELECT. It combines a range of functionality into one complex command. Used primarily to retrieve data from the database. Also used to create copies of tables, create views, and to specify rows for updating. General Format: Generic overview applicable to most commercial SQL implementations - lots of potential... Readmore

TABLES CREATE TABLE Define the structure of a new table Format: CREATE TABLE tablename ({col-name type ,...}); The 'constraint' clause in the CREATE TABLE statement is used to enforce referential integrity. Specifically, PRIMARY KEY, FOREIGN KEY, and CHECK integrity can be set when you define the table. The syntax for key and check constraints is shown below. {PRIMARY KEY | FOREIGN KEY} (local-field) } for attribute constraints... CHECK (condition) Example: Define the student table CREATE TABLE... Readmore

Many database management systems support some version of structured query language (SQL). In some DBMSs (i.e., ORACLE) SQL is the primary data manipulation interface. Consequently, SQL is a very important topic. The purpose of this document is to introduce you to the major SQL statements and to show you how they work. This document will concentrate primarily on ORACLE SQL; however, some attention also will be given to other versions of SQL. This is not a complete reference of SQL. If you are... Readmore

Manajemen Struktur Basis Data Tanggung jawab DBA dalam menangani struktur basisdata adalah: Merancang skema DBA biasanya tidak terlibat dalam perancangan basisdata mulai dari awal. Oleh karena itu, setiap terjadi perubahan struktur basisdata yang berpengaruh pada skema / relasi antar tabel harus selalu dicatat Mengawasi terjadinya redundancy Redundancy dapat terjadi pada dua hal, yaitu performance dan data integrity. DBA harus menetapkan prosedur tertentu untuk melakukan rekonsiliasi data untuk... Readmore