Scribd
Upload
8 views1 page

SQL Table Creation and Data Insertion

The document contains SQL commands to create and manipulate two tables: WorkerSkill and student. It includes various INSERT statements to populate the WorkerSkill table with worker details and the student table with student information. Additionally, it defines a newstudent table with constraints on the fields, ensuring uniqueness and validity of the data entered.

Uploaded by

divya28032006
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views1 page

SQL Table Creation and Data Insertion

The document contains SQL commands to create and manipulate two tables: WorkerSkill and student. It includes various INSERT statements to populate the WorkerSkill table with worker details and the student table with student information. Additionally, it defines a newstudent table with constraints on the fields, ensuring uniqueness and validity of the data entered.

Uploaded by

divya28032006
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

create table WorkerSkill(Name varchar(25),Skill varchar(25),City

varchar(20),PhoneNumber varchar(20) );
INSERT INTO Workerskill VALUES('DICK JONES','SMITHY','TRICHY','91-0437-77651');
INSERT INTO Workerskill VALUES('JOHN PEARSON','COMBINE DRIVER','CHENNAI','91-0426-
98721');
INSERT INTO Workerskill VALUES('HELEN BRANDT','COMBINE DRIVER','MADURAI','91-0435-
33333');
INSERT INTO Workerskill VALUES('JOHN PEARSON','COMBINE DRIVER','CHENNAI','91-0453-
98765');
INSERT INTO Workerskill VALUES('JOHN PEARSON','SMITHY','CHENNAI','91-0345-34565');
INSERT INTO Workerskill VALUES('VICTORIA LYNN','SMITHY','SYDNEY','91-0234-98723');
INSERT INTO Workerskill VALUES('ADAH TALBOT','WORK','THANJAVUR','91-0652-66544');
INSERT INTO WorkerSkill VALUES('ELBERT LOWELL','DISCUS','DELHI','91-0433-90875');
INSERT INTO Workerskill VALUES('WILFRED LOWELL','WORK','SALEM','91-0213-98723');
INSERT INTO Workerskill VALUES('ANAND KUMAR','PROGRAM','ERODE','91-0441-98123');
INSERT INTO Workerskill VALUES('JACKSON','PROGRAM','HOSUR','91-0543-90873');
select * from Workerskill;
create table student ( REGNO VARCHAR2(20),
VARCHAR2(20),
DOB DATE,
MARK NUMBER(3));
desc student;
insert into student (regno) values('104');
insert into student (regno,name) values ('105','ganesh');
select * from student;
insert into student values('107','mathioli','12-dec-20',75) ;
select instr('sastra','a') from dual;
create table newstudent(
regno number(10) primary key,
name varchar(20) unique,
dob date not null,
mark number(3) default 0 check(mark > 0 and mark < 100));

Common questions

Powered by AI

'Create table' and 'insert into' statements help clarify assumptions by defining data types, constraints, and relationships explicitly in the schema. They show the intended structure and logic for data handling, which guides proper data insertion and ensures integrity across operations. They also highlight intended data types, constraints, and any assumptions about input data.

Introducing composite keys, such as combining 'Name', 'Skill', and 'City' as keys, can effectively eliminate redundancy by ensuring each entry is unique not only by person or skill but also by location. This prevents duplicate records like multiple contact numbers for the same person, ensuring data uniformity and reducing redundancy across records, enhancing data accuracy and consistency.

The primary key constraint on 'regno' ensures that each student registration is unique and identifiable, preventing duplicate entries which enhances data integrity. The unique constraint on 'name' ensures no two students can share the same name, which maintains data uniqueness but might not be practical given common names. The combined use of these constraints effectively supports data integrity but may need reconsideration due to real-world uniqueness needs.

Using VARCHAR2 for registration numbers and names offers flexibility in storing variable-length text efficiently, as it only uses space for the actual length of the input text. However, VARCHAR2 may also lead to higher fragmentation and is less efficient for fixed-length values compared to fixed-length types, which could be problematic if all values are of uniform length.

Inserting data without specifying all fields assumes default values will be applied to unspecified fields or NULL values unless constrained by table design. This can streamline data entry when defaults are sufficient, but it risks incomplete or unintended data entries if constraints or defaults are not properly set, potentially leading to integrity issues.

'Select instr('sastra','a') from dual;' is likely used to demonstrate or test string manipulation functions within the SQL environment. It finds the position of the first occurrence of character 'a' in the string 'sastra'. This function can be useful for string parsing and validation within SQL queries, aiding in data text analysis tasks.

The check constraint on the 'mark' field (ensuring marks are between 0 and 100) ensures data integrity by preventing unrealistic values. However, if not properly validated before insertion, this constraint can cause errors during data entry, possibly rejecting valid entries due to validation issues elsewhere, such as negative or overly positive marks.

The 'varchar(25)' type is used for Name and Skill fields to accommodate varied text lengths while optimizing storage space. Varchar allows for flexible storage of different lengths of text up to 25 characters, which is typically sufficient for names and skills, helping maintain efficient storage and retrieval.

Duplicate entries for individuals like 'JOHN PEARSON' with different phone numbers could lead to data inconsistencies and misunderstandings in contact information. This inconsistency can affect how data is processed or analyzed, potentially leading to decisions based on inaccurate data.

To prevent duplicate entries, the WorkerSkill table could be modified by creating a composite primary key using both 'Name' and 'City', assuming that an individual should not have multiple entries within the same city for the same skill. Alternatively, a unique constraint could be applied on a combination of the Name and PhoneNumber fields.

You might also like