1. How can we create table?
Sol) Create table in SQL is using DDL (Data Defination Language) command using a CREATE TABLE statement. Table contain columns and constraints. In Table-level constraints specify a column or columns, columns have a data type and specify column constraints i.e., Column-level constraints)
Syntax:
CREATE TABLE <table_name>(column1
datatype column_constraint,
column2 datatype column_constraint,
------
------
columnN datatype column_constraint,
table_constraint);
Where <table_name) specifies new table name and create columns(column1,column2,...columnN) with datatype and and column_constraint(optional) you need to separate them by commans. The datatype are NUMBER,VARCHAR2,etc., and column constraint are NOT NULL, PRIMARY KEY, CHECK. If you need apply table constraints(optional) i.e., PRIMARY KEY, FOREIGN KEY, CHECK.
Example: Creating new table using CREATE TABLE Statement
CREATE TABLE employee(id NUMBER,name VARCHAR2(20) NOT NULL,salary NUMBER(5,2), PRIMARY KEY(id));
Here, column(id) is NUMBER type, column(name) is VARCHAR2(20) with column-level constraint NOT NULL and column(salary) is NUMBER with precision(decimal) values and finally table-level constraint is PRIMARY KEY(id)
2. What are the various data types available using
SQL?
Sol)
Data type: Data type is associated with a specific storage format,
constraints and a valid range of values. Each column value and
constant in a sql statement has a datatype. In ORACLE provides
built-in data types.
1. Character Datatype
2. Numeric Datatype
3. Date Datatype
4. LOB Datatype
5. RAW Datatype
6. ROWID Datatype
1. Character Datatype: Character datatypes store characters
(both alpha and numerical) data in strings, with byte values
corresponding to the character encoding scheme such as 7-bit ASCII,
EBCDIC, etc., support oracle. In Character Datatypes are two types
(CHAR or VARCHAR2)
a) CHAR datatype stores fixed-length character
strings, you must specify a string length (in bytes or characters)
between 1-2000 bytes for the CHAR column
width. The default is 1 byte.
b) VARCHAR2 datatype stores variable-length character strings, you must specify a string length (in bytes or characters) between 1-4000 bytes for VARCHAR2 column. The default maximum size 50 characters.
2. Numeric Datatype: In Numeric Datatype store
positive and negative fixed and floating-point numbers from
0-infinity. In this Numeric Datatype contains NUMBER datatype and
floating-point numbers. In ORACLE DATABASE upto 38 digits of
precisions.
syntax: column_name
NUMBER(digits,decimal_points)
3. Date Datatype: The DATE datatype stores
point-in-time values(dates and times) in a table. The DATE datatype
stores the year(including the century), month, day, hours, minutes
and seconds.
syntax: column_name DATE
4. LOB Datatype: The LOB(Large OBject)
datatypes BLOB(Binary Large OBject), CLOB(Character Large OBject),
NCLOB(National Character Large OBject) and BFILE(external binary
file stored outside of the database), you can store
and manipulate Large blocks of unstrctured data such as
text,graphic images, video clips and sound waveforms.
5. RAW Datatype:RAW datatype used to store
binary data or byte strings, it is variable-length datatype like
tha VARCHAR2 characters. RAW data is always returned as hexadecimal
character value. Actually, there are two types RAW and
LONG RAW datatypes. LONG
RAW datatype can be used to store graphics, sound,
documents or arrays of binary data.
6. ROWID Datatype: ROWID datatype stores the
address(rowid) of every row in the database. Rowid are two
types
a) Physical rowid: This is store addresses of rows
in ordinary tables (excluding index-organized tables), clustered
table, table particitions and subpartions, indexes and index
partitions and subpartitions.
b) Logical rowid: This is store the addresses of
rows in index-organized tables.
Another type of rowid is UROWID (Universal rowid) is support both
logical and physical rowids.
Example: SELECT ROWID, name FROM employee;
3. What are the various constraints that you can create.
What is the difference between table level constraints and column
level constraints?
Sol) Types of Constraints:
a) PRIMARY KEY (Unique and NOT NULL)
It is defined column or columns that uniquely identify a given row
in a table. It is also define a table, you can define a single
primary key on a table.
b) FOREIGN KEY:
This constraint used to relationship between two tables.
c) NOT NULL
This constraints are in-line constraints the that indicate that
column can not contains NULL values.
d) CHECK
This constraints validate that values in a given
column meet a spefic criteria.
e) UNIQUE
This constraints define a column or columns that must
be unique in value.
Difference between Table-level and Column-level constraints:
Table-Level Constraints:
This type of constraint is checked if there is any modification to
a row, the value of the column changed or not. Example CHECK
constraint, if we create CHECK constraint in table level the
constraint will be check each time the row has been affected by any
type of change.
Example:
CREATE TABLE employee(ID NUMBER PRIMARY KEY,NAME
VARCHAR2(30),AGE NUMBER NOT NULL,SALARY NUMBER(10,2), CONSTRAINT
TABLE_LEVEL_CONSTRAINT CHECK(AGE BETWEEN 19 AND
45));
Column-Level Constraints:
This type of constraint is check when the value of the
column changed.
Example:
CREATE TABLE employee(ID number PRIMARY KEY,NAME
VARCHAR2(20),AGE NUMBER,SALARY NUMBER(10,2), CONSTRAINT
COLUMN_LEVEL_CONSTRAINT CHECK(AGE>18))
How can we create table? What are the various data types available using sql What are...
In oracle sql, how do I add "not null constraint" at end of create table block and give it a name without using the CHECK constraint? Is it e.g., CONSTRAINT constraintName NOT NULL If so, then how does the statement know which columnName I'm referring to to make NOT NULL? I know that I can do this after declaring the column, what I'm wondering about is how to do this after declaring all the columns.
Oracle SQL Step 9. Create the table below and insert data LFM MoreSkill table SkillcodeSkillname Primary KeyUnique BD Build Databases Design Databases PL/SQL Programming Machine Learning ML Step 10. Using set operations, list what skills (10a) appear in the MoreSkill that were not in Skill (10b) appear in the Skill that are not in MoreSkill (10c) appear in both Skill and MoreSkill (10d) appear in either table so you have a complete list of all skills
Write the SQL statement to create a five-field table to contain sample student information. The constraints that need to be satisfied by the attributes of this table are as follows: the attribute First_Name is mandatory and can have up to 16 characters, the attribute Last_Name is mandatory and can have up to 16 characters, the attribute Date_Of_Birth is of type date is required, the StarID field identifies each student and is 8 characters long the GPA field is numeric that...
Sample data for these tables are shown in Figures 3-26 and 3-27. For each SQL statement you write, show the results based on these data. If possible, run the statements you write for the questions that follow in an actual DBMS, as appropriate, to obtain results. Use data types that are consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types by using either the MySQL, Microsoft SQL Server, or Oracle...
Write an SQL CREATE statement to create the structure of a table to store the following employee information empLastName, empFirstName, Dept (foreign key to department table), emp Address, empZip, empCity, empState. Use appropriate data types to reflect the data which will be stored in each attribute. Identify attributes which should not be null by using the correct SQL statements in the create statement
SQL Question 2 Using your table from question 1: Create a minimum of 4 INSERTS. On one of your INSERTs, do not specify all of your columns. In other words, if you have 5 columns in your table, only mention 4 or less columns in one of your INSERTs. Be sure to write a comment above the INSERT with less columns and explain what is missing in a full English sentence. You should write a simple SELECT after your INSERTs...
Create a SQL statement in Oracle using the correct comparison operator in the following situation. (A full statement that can execute is REQUIRED) Screenshot the code ONLY and attach. Retrieve the product_name and the price from the PRODUCT table for any product whose price is less than or equal to $50.00
Oracle 12c SQL - Chapter 5: Case Study: City Jail, Part II Note: You are limited to using the Oracle Live interface to create solutions for the problems below. SQL Live does not allow as many commands, statements, and symbols as full Oracle SQL. Specifically, the ampersand substitution variable symbol (&) may not be used for any of the solutions below because Oracle Live does not recognize it. Oracle Live is available to use here: https://livesql.oracle.com/apex/f?p=590:1000 Execute the CityJail_5.sql script...
I am supposed to create the code in SQL for the 3 Triggers using the information on the table below. 13.. Assume the Branch table contains a column called ?Total value that-represents the -total price. for all books.at -that branch. Following ?the style shown in-the text, write the code for the following-triggers. a.-When ?inserting a row in the Copy table, -add -the price -to the -total -value for the appropriate branch . b. When-updating -a -row in-the Copy table, .add-the-difference...
(SQL) Write DDL statements to create the tables below and use appropriate data types for the attributes.Relational database schema: Teacher = [CourseN, Quarter,TeacherName] Course = [CourseN,CourseName, Nunit) LocationNTime = [CourseN, Quarter , DayTime, RoomN] . //Examples of DayTime: M2:00AM, W4:50PM, and T8:00PM. Note that DayTime is represented as a string. Student = [studentName, CourseN, Quarter] .................... The DDL statement must include at least the following constraints: Every Primary Key; Every Foreign Key; For every Foreign Key constraint, the referential integrity...