Premium Essay

Sql Coding

In: Computers and Technology

Submitted By pewe
Words 377
Pages 2
1. Write SQL code to create a STUDENT table that includes the following attributes:
• Student ID
• First Name
• Last Name
• Street Address
• City
• State
• Zip Code
• Phone
• Email
In the code remember to include data type, data size, and if that field is required or not. The selected data types and data size should be logical. For example you cannot use Char (10) for street address; size 10 is very small compared to many long street addresses that we deal with every day. Having a short size data will create error messages.
1. Create table STUDENT (
StudentID Number (1,1) not null,
FirstName Char(30) not null,
LastName Char(30) not null,
StreetAddress Char(50) not null,
City Char(30) not null,
State Char (15) not null,
ZipCode Number(5,0) not null,
Phone Char(12) not null,
Email Char(100) not null,
Primary Key (StudentID));

2. Write SQL statement to :
• Insert at least three rows of fake data (any names, any addresses,…) into the table above.
• Add a column called StudentStatus to the table above.
• Add a new constraint to limit the student zip code to be within the following group (32117, 32116, 32115, 32114)
• Count the number of rows in the table
• Show the first 3 records of data.
• Delete the column StudentStatus.
• Create a view that shows only First Name, Last Name, and Email
• View records of students living in area code 32116.
• Delete the table student

2.
1. Insert into STUDENT values (
‘1’, ‘Frank’, ‘Redman’, ‘18 RidgeWood ave’, ‘Ormond Beach’, ‘FL’, ‘32174’, ‘234-343-2134’, ‘RedmanF@gmail.com’);
2. Insert into STUDENT values (
'2', 'James', 'Richard', ’23 conqunia ridge way’, Ormond Beach’, ‘FL’, ‘23145’, '555-537-7654', 'Richard.James@somewhere.com');
3. Insert into STUDENT values (
'3', 'Frier', 'Liz', ’65 seaton valley’, ‘Palm Coast’, ‘FL’, ‘23342’, '555-537-6543', 'Liz.Frier@somewhere.com');
4.

Similar Documents

Premium Essay

Cis 499 Sql Coding Senior Seminar

...SQL Coding Proj. Draft Building of Tables and Inserting Values PARENTS TABLE The building of the tables were done in SQL which is a program that the PL/SQL coding is done in that talks to the computer to let the computer know exactly what it needs to create for this particular table. The tables were created for formality purposes to keep the assistant from doing any documentation of parents and children on paper. This saves a lot of time and writing. As a child enrolls in the facility, there information and their parent(s) information is put into the system and created. Once created, I then entered the information that was called out in the creation of the tables. This information is shown below. SQL> CREATE TABLE PARENTS( 2 parent_id numeric(5), 3 gender_code varchar2(8), 4 marital_status_code varchar2(15), 5 first_name varchar2(25), 6 last_name varchar2(30), 7 address varchar2(35), 8 city varchar2(25), 9 state varchar2(2), 10 zipcode numeric(5), 11 email_address varchar2(35), 12 phone_number numeric(10)); Table created. SQL> INSERT INTO PARENTS (parent_id, gender_code, marital_status_code, first_name, last_name, addres s, city, state, zipcode, email_address, phone_number) 2 VALUES ('1100','Female','Single','Kim','Jones','453 Dayton Drive','Midfield','AL','35667','KJon es@yahoo.com','2056377454'); 1 row created. SQL> INSERT INTO PARENTS(parent_id, gender_code, marital_status_code, first_name, last_name...

Words: 1287 - Pages: 6

Premium Essay

Pos410Wk2

...Learning Team:  Create Database, Table & Inserts   Read and follow the instructions carefully and completely for submission of the Week 3 Learning Team Assignment.   NOTE: This is a large assignment.  Starting early in the week and giving your team time to post code for a review and assistance will help you prepare it accurately and completely.  I encourage everyone to work on the assignment throughout the week and not wait until the day it is due to begin the work.  Please see the Instructor Policies for Team Participation credit guidelines.   REQUIRED: Coding Standards for this course are required.  Please see the attached Coding Standards.   CRITICAL Resource:  The attached Errors Table is a resource for dealing with error messages and other potential obstacles you may encounter writing code.    Optional: Read the Week 2 and 3 Handouts in the Recommended Learning Activities to assist with creating this assignment.   Optional: Complete the Week 2 and 3 Learning Activity Tutorials in the Recommended Learning Activities.   REQUIRED:  Use the Kudler Chart of Accounts PROVIDED.  Analyze the "PROVIDED" Chart of Accounts spreadsheet in order to decide how you will design the database.   Plan your Data Dictionary (You will be submitting data dictionary with your final project submission, Week 5.)   REQUIRED:  Use the Data Dictionary Template attached to create the Data Dictionary for the Learning Team Assignment.   REQUIRED: Follow the instructions in the provided...

Words: 1276 - Pages: 6

Free Essay

Hibernate

...Hibernate allows us to do object relational mapping. Simply this mean, conversion of objects derived from java classes inside the application, directly to tables in the database. This will reduce the effort utilized in drawing ER diagrams, normalization (conceptual design) as well as physically writing SQL coding to complete the physical design inside the RDBMS. Hibernate will prevent getting your java code dirty via writing SQL within it. If you use hibernate, no sql within your java application at all. Also hibernate provides:- • Caching of frequently used data record. Its several times efficient than retrieving from the database. • Needn’t opening and closing database connections. This will prevent memory leakage problems • Allow the java application to work with multiple DBMSes. • Small change in the java code will alter the table in the DB. Needn’t dropping the table. • 80% of code reduction in the application program When the required operation was initiated from the application, hibernate is versatile enough to auto generate the required SQL coding Go to this site called www.hibernate.org/downloads After going into the site .. notice some changes Look at the release bundles .. under the release bundle , download the latest version Add those into the library.. all those jar files to the library And then add a xml file and name as hibernate.cfg And then go to project file and search for a file in the name of hibernate.cfg.xml And from that get the...

Words: 309 - Pages: 2

Premium Essay

Srs Formate

...INTRODUCTION TO RTO MANAGEMENT In this growing competitive world it is becoming increasingly & absolutely necessary for any organization to streamline all its activities all the way stressing on quality perfection. Quality can be defined as fitness for use at an economical cost while satisfying customer requirements this can be achieved if the organization has the good quality system in which all the medical activities of the hospital are bought in line with operating procedure and guidance. So the RTO management is planned to automate the current manual system. The main aim of the project is to design innovative software, which deals with the RTO management system. The motto of the project is to simplify the job of the administrative people and to render a user-friendly package. The system provides information regarding the RTO application and its status. The tedious jobs such as verifying all the records of the applicant, confirming all the personal details are furnished, submission of qualification documents, driving license, registration details, etc., are done in the most convenient way to the administrator. Also security is being provided in the most proficient way. All the intermediate stages starting from receiving of the application form to revealing the applicant number along with the expiry date of the license are being dealt. Advantages  ⇨ The main purpose of this software is to make RTO automated...

Words: 10261 - Pages: 42

Premium Essay

Media

...salary details, perform various operations like insert, delete, update, search, and prepare necessary report. TABLE DESCRIPTIONS: ➢ SQL> create table payroll(Empno number(10),empname char(15),dept_id number(5),dob date, BP number(5),DA number(5),HRA number(5),PF number(5),CCA number(5),MA number(5),LOAN number(5),Grosspay number(5),Netpay number(5)); ➢ SQL>desc payroll; Name Null? Type ------------------------------- -------- ---- EMPNO NUMBER (5) EMPNAME CHAR (15) DEPTID NUMBER (5) DOB DATE BP NUMBER (5) DA NUMBER (5) HRA NUMBER (5) PF NUMBER (5) CCA NUMBER (5) MA NUMBER (5) LOAN NUMBER (5) GROSSPAY NUMBER (5) NETPAY NUMBER (5) ER DIAGRAM: CODING: ADD NEW: Private Sub fa_Click() Text1.SetFocus Adodc1.Recordset.AddNew End Sub DELETE: Private Sub fd_Click() ...

Words: 331 - Pages: 2

Premium Essay

Blue

...efficient at using the Internet. They want to use just-in-time supply chain methodologies. Companies want to use customer relationship marketing systems and have wireless access to corporate data. Instead of updates once a day, they want to be able to take a snapshot of their business more frequently.” The Internet has also added another dimension to database management, because knowledge management becomes more crucial as online customers require accurate, real-time information. "One of the problems with business-to-consumer e-commerce has been accuracy of data," Hammer says. "Some consumers were finding that they could not reliably order from online retailers, and that really hurt the business.” ETI recently introduced Accelerator for SQL/Teradata, which speeds the transformation and exchange...

Words: 1255 - Pages: 6

Premium Essay

Assignment1

...You can experiment with saving and running command files. If you have any questions at any time, ask your instructor. Practice 1 Test your knowledge: 1. Initiate an iSQL*Plus session using the user ID and password that are provided by the instructor. 2. iSQL*Plus commands access the database. True/False 3. The following SELECT statement executes successfully: SELECT last_name, job_id, salary AS Sal FROM employees; True/False 4. The following SELECT statement executes successfully: SELECT * FROM job_grades; True/False 5. There are four coding errors in the following statement. Can you identify them? SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; Practice 2: Overview In this practice, you build more reports, including statements that use the WHERE clause and the ORDER BY clause. You make the SQL statements more reusable and generic by including ampersand substitution....

Words: 520 - Pages: 3

Premium Essay

Airline

...Project Report On Session (2004-2005) For the partial fulfillment of requirement of degree of Bachelor of Science in Information Technology (B.Sc – IT). Submitted By: Submitted To: Ms. Sandeep Bassi Lyallpur Khalsa college, Jalandhar City. (Affiliated To Guru Nanak Dev University) Tables of Contents ❖ Acknowledgement 4 ❖ Bonafide Certificate 5 ❖ Introduction 6 o Purpose o Design Constraints o Scope Of Project o Problem Definition ❖ Software Process 10 o Objective o Model Of Project ❖ Software Requirement Specification 12 o System Analysis o Functional Requirements o Performance Requirement o Hardware & Software Requirements ❖ Software Interface 23 ❖ Project Scheduling 46 o Pert Chart o Gantt Chart ❖ System Design 49 ...

Words: 9647 - Pages: 39

Premium Essay

Report

...Project Submitted to the Al Musanna College of Technology in partial fulfillment of the requirements for the award of the Degree of Higher Diploma in Internet and E-Security Submitted by: (1) 2782011- Abdullah ALJaradi - “Leader” (2) 2782239 - Auf ALMujaini Project Supervisor : Mrs.Divyajyothi M G Al Musanna College of Technology Declaration We hereby declare that the project entitled “Banking Security System “ submitted for the Higher Diploma in Internet and E-Security is our original work and this project work has not formed the basis for the award of any degree. Name of the students | Signature | Abdullah Aljaradi | | Auf Almujini | | Date of submission:- | Place:- | Acknowledgment We would like to express our deep thanks and appreciation to all who have supported us by providing a good and encouraging atmosphere to successfully complete our Higher Diploma project . Thanks to Allah for granting us with the necessary patience to finish our project successfully. we hope it will be successful. Our profound thanks to our family for granting us a suitable and comfortable home environment in order to work on our project. Our sincere appreciation is extended to the technician Mr.Nasser Ali for his continuous guidance and support throughout the course of this project. Thank you dear sir for your concerned guidance and good instructions. Many thanks to Mrs.Divyajyothi for...

Words: 1256 - Pages: 6

Premium Essay

Questions to Ask an Oracle Developer

...issues with working on all phases of a project (such as Analysis, Design, Coding, Documentation and Implementation)? Under your current role, do you work in all these phases yourself or are you usually involved with one particular phase? General Oracle Database and PL/SQL Questions: • Do you have any experience with Autonomous Transactions in Oracle database? The purpose is to complete (commit/rollback) a transaction in a called procedure irrespective of the transaction state in the calling procedure. • Have you ever encountered a situation with Mutating Tables and what did you do to work around it? When a table is in state of transition it is said to be mutating. eg: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select. • What’s your experience with Oracle Forms and Reports. Where would you implement bulk of business rules so as to make your coding more modular in Oracle Forms? PLL’s (PL/SQL Libraries). • What is referential integrity? Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table. • What is a Transaction in Oracle? A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. • What is a Cursor in Oracle? A cursor...

Words: 1951 - Pages: 8

Free Essay

Advantages of a Relational Database

...Advantages of a Relational database Relational databases are the standard when creating industrial standard efficient and scalable databases. They are based on a structure of tables which are logically connected to each other and can be queried in complex way to extract relevant and meaningful information. The querying of a relational database is done using a high level language called SQL which stands for structured query language. SQL is very easy to understand and unlike traditional programming languages does not rely on arcane syntax, rather structured query language uses English like meaningful syntax in its operations. As opposed to flat file databases this logical organization of data across tables in the form of columns greatly simplifies the organization and retrieval of highly complex data. As the business needs of the client rises so will the size and complexity of data, on of the biggest advantages of relational databases is the scalability associated with them, so as the company grows expanding the database will not be a problem and making sense of the data and extracting meaningful information from it will only get slightly harder. Relational databases come with a plethora of security options and views can be enforced to ensure that unauthorized access to the sensitive information in the database is not allowed. Relational databases also avoid redundancy of information and such databases can be highly optimized by methods such as normalization and good database...

Words: 652 - Pages: 3

Free Essay

20th Foot Musters

...Thomas WILLIAMS – service record extracted from Muster Rolls of 20th Foot regiment 1812 May Active in Newry “Volunteer from Royal Westminster Militia paid by them to 6th May” (as a private) June Active in Newry July “From Private” (to Drummer on 25th). “Sent recruiting To Bungay, Suffolk, Eng paid by me to 25th” August Recruiting in Bungay September Recruiting in Bungay October In red: “Cordwainer at St George, Middlesex” November Recruiting in Bungay December “To recruiting company” 1813 January Recruiting in Bungay February Recruiting in Bungay March Recruiting in Bungay April Recruiting in Bungay May Recruiting in Bungay June Recruiting in Bungay July Recruiting in Bungay August Recruiting in Bungay September Recruiting in Bungay October Recruiting in Stowmarket November Recruiting in Stowmarket December Recruiting in Stowmarket 1814 January Recruiting in Stowmarket (Regiment shown as being in Totnes) February Recruiting in Stowmarket March Recruiting in Stowmarket April Recruiting in Stowmarket May Recruiting in Stowmarket June Recruiting in Stowmarket July Recruiting in Stowmarket “The regiment being at home this manning will be discontinued on the rolls from 25th July” (home depot = Knightsbridge) August Recruiting in Stowmarket (Regiment shown as ‘on ship’) September Recruiting in Stowmarket October Recruiting...

Words: 829 - Pages: 4

Premium Essay

Nt1330 Unit 3.1 Problem Analysis

...PROBLEM FORMULATION 3.1 Problem Statement Low density parity check codes are forward error correcting codes. The LDPC block codes are inefficient, since a new code must be hypothesized each time a change in frame size is desired. A number of algorithms with varying complexity and performance have been proposed for LDPC decoding. But achieving a balanced trade-off between decoding performance and implementation complexity still remains a potential problem. LDPC decoding algorithms operates by making either hard decision or soft decision on the message received from the noisy channel [20]. 3.1.1 Sum product Algorithm The sum product algorithm for LDPC decoding is a soft decision message passing algorithm. In case of soft decision based algorithms, the input data to the decoder is the channel probabilities, represented in logarithmic ratio which is known as log-likelihood ratio (LLR). This algorithm requires LLR for variable node operations to make decoding decisions. The LLRs are transferred over to the variable nodes (V), this variable node carry out the sum operation on the input LLRs as in equation (1) and computed messages are passed along the connected edges to the check nodes (C). SPA Variable node operation: V_i=〖LLR〗_n+∑_(j≠i)▒C_j (1) Where n=1,2,. . . .number of variable nodes i, j=1,2,. . . .degree of variable node The operation performed by the check nodes (C) is given in equation (2). The check nodes also perform...

Words: 910 - Pages: 4

Premium Essay

Data Base

...Database Design Table of Contents CHAPTER 1 .............................................................................................................................................. 2 INTRODUCTION ................................................................................................................................... 2 DATABASE MANAGEMENT SYSTEMS .................................................................................................. 2 Database ......................................................................................................................................... 2 Database Management System (DBMS) ......................................................................................... 2 Schemas, Instances and Data Independence.................................................................................. 3 DATA MODELS..................................................................................................................................... 3 Hierarchical Model .......................................................................................................................... 3 Network Model ............................................................................................................................... 4 Relational Model ............................................................................................................................. 5 CHAPTER 2 ............................................

Words: 4347 - Pages: 18

Premium Essay

Db2 Concepts

...DB2 & SQL Concepts 1.2.1 What is SQL? Structured Query Language (SQL) is a standardized language for defining and manipulating data in a relational database. In accordance with the relational model of data, the database is perceived as a set of tables, relationships are represented by values in tables, and data is retrieved by specifying a result table that can be derived from one or more tables. DB2 transforms the specification of a result table into a sequence of internal operations that optimize data retrieval. This transformation occurs when the SQL statement is prepared. This transformation is also known as binding. 1.2.2 Static SQL The source form of a static SQL statement is embedded within an application program written in a host language such as COBOL. The statement is prepared before the program is executed and the operational form of the statement persists beyond the execution of the program. A source program containing static SQL statements must be processed by an SQL precompiler before it is compiled. The precompiler checks the syntax of the SQL statements, turns them into host language comments, and generates host language statements to invoke DB2. 1.2.3 Dynamic SQL A dynamic SQL statement is prepared during the execution of an SQL application, and the operational form of the statement is not persistent. The source form of the statement is a character string...

Words: 30246 - Pages: 121