Sql File

In: Computers and Technology

Submitted By simran22
Words 252
Pages 2
Index:



S. No. Content Page No.
1. Screen shots of Queries 4-16

2. References 17






























QUERY 1
Select name,address from
(
select
CONCAT(emp_fname,' ',emp_lname) as name ,
CONCAT(address_st,', ', address_city, ' ',address_state, ' ', address_postcode ) as address,
emp_salary_hourly as salary
from employee,casual,address
where (employee.emp_id = casual.emp_id
and
employee.address_id_postal= address.address_id)
union
select
CONCAT(emp_fname,' ',emp_lname) as name ,
CONCAT(address_st,', ', address_city ' ',address_ state, ' ',address_postcode ) as address,
emp_salary_yearly as salary
from employee, fulltime, address
where
(employee.emp_id = fulltime.emp_id and employee.address_id_postal= address.address_id)) temp
order by salary ;


[pic]

QUERY 2

select CONCAT(Cust_FName ,' ' , Cust_LName) as Name, CustOrd_Date from CUSTOMERORDER, CUSTOMER where (CUSTOMERORDER.Cust_Number = CUSTOMER.Cust_Number) order by CustOrd_Date desc limit 1 ;


[pic]
















QUERY 3


select
str_name,CONCAT(address_st,',',address_city, '',address_state,' ',address_postcode)as address
from store,address,
where store.address_id= address.address_id)
order by str_name;



[pic]















QUERY 4


select Cust_Number , CONCAT(Cust_FName,' ',Cust_LName) as Name from CUSTOMER where Cust_Number Not In ( select CUSTOMER.Cust_Number from CUSTOMER, CUSTOMERORDER where CUSTOMER.Cust_Number = CUSTOMERORDER.Cust_Number ) ,





[pic]











Query 5
select CONCAT(emp_fname,' ',emp_lname) as name from employee where emp_id in( select emp_id from jobtype, casual where ( jobtype.jobtype_id =…...

Similar Documents

Database - Sql

... it from a backup file. 10. Define: a. Statement of Work * A statement of what needs to be done often including a history of the problem, a statement of scope, objectives of the project, timelines, and deliverables. b. Scope * What will and what won't be in the database? Only what is known and can be controlled well. Avoid wrong and maybe outdated data. Create for example an interface with an other system, where that data is inside the scope. c. Stakeholders * Some one who has a “stake” in the success or contents of the database. d. Requirements * Something of a database or program must do to fulfill its function. e. Security Requirements * Some or all of the data in a database is confidential, typically contains core business information, financial or legal risk if reveal to the wrong person. An essential requirement of most databases is to develop a security schema that determines who has access to what data. Anyone without the proper authentication credentials should be excluded. 11. Describe some ways in which you would gather requirements for a database. * Set up a meeting with your clients to gather their information or their requirements for the creation of the database * Show up at the job site and take visual notes * Talk to the employees and staff * Look online * Use past jobs completed as a reference 12. What is an ERD? Is it considered to be the physical...

Words: 2656 - Pages: 11

File

... ill patients filed a lawsuit saying the law interfered with the right to die. They argued that people who are dying from painful illnesses have a right to end their lives with dignity rather than suffer until death. The Supreme Court disagreed. It said the right to die in Cruzan was a right to refuse medical treatment. The right of privacy does not include a right to be killed with medical assistance. I agree with the judges’ decision to involve family members in order to allow the state to separate an individual from life support only if the victim wants to and is allowing it. I also agree with the judge to make sure that the victim gives a sign to approve the removal of life support. What I don’t agree is the judges’ decision to allow the medical staff to slowly deteriorate the victims health with medicinal procedures. The medical staff and hospital should not be allowed to slowly kill a victim by giving them medicine. It’s against the constitutional law. SHOARS v. EPSON AMERICA, INC. High-Tech Monitoring Accoring to the book, Americans are protected by privacy laws to a certain degree. The federal Omnibus Crime Control and Safe Streets Act of 1968 regulates the government's use of wiretapping to listen to telephone conversations. The Privacy Protection Act of 1974 and the Freedom of Information Act require the government to be fair when it collects, uses, and discloses private information. Sometimes, however, people file lawsuits saying...

Words: 2131 - Pages: 9

Sql Homework

...Question 1 select [book_code],[title],[book].[publisher_code],[publisher].[publisher_name] from[book],[publisher] where [book].[publisher_code]=[publisher].[publisher_code] order by [PUBLISHER_NAME] ; 33 rows returned 3906 The Soul of a New Machine BY Back Bay Books 8092 Godel, Escher, Bach BA Basic Books 3350 Group: Six People in Search of a Life BP Berkley Publishing 669X A Guide to SQL CT Course Technology 2908 Electric Light FS Farrar Straus and Giroux 0189 Magic Terror FA Fawcett Books 9931 To Kill a Mockingbird HC HarperCollins Publishers 9882 Slay Ride JP Jove Publications 8720 When Rabbit Howls JP Jove Publications 0808 The Edge JP Jove Publications 3743 Nine Stories LB Lb Books 6908 Franny and Zooey LB Lb Books 9883 The Catcher in the Rye LB Lb Books 9701 The Grapes of Wrath PE Penguin USA 7405 East of Eden PE Penguin USA 2766 Of Mice and Men PE Penguin USA 5163 Travels with Charley...

Words: 408 - Pages: 2

Sql Tutorial

... compression Parallel Execution Analytic SQL Data mining Partitioning TUTORIALS POINT Simply Easy Learning MS ACCESS This is one of the most popular Microsoft products. Microsoft Access is an entry-level database management software. MS Access database is not only an inexpensive but also powerful database for small-scale projects. MS Access uses the Jet database engine, which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL). MS Access comes with the professional edition of MS Office package. MS Access has easy-to-use intuitive graphical interface.     1992 - Access version 1.0 was released. 1993 - Access 1.1 released to improve compatibility with inclusion of the Access Basic programming language. The most significant transition was from Access 97 to Access 2000. 2007 - Access 2007, a new database format was introduced ACCDB which supports complex data types such as multi valued and attachment fields. Features:    Users can create tables, queries, forms and reports and connect them together with macros. The import and export of data to many formats including Excel, Outlook, ASCII, dBase, Paradox, FoxPro, SQL Server, Oracle, ODBC, etc. There is also the Jet Database format (MDB or ACCDB in Access 2007), which can contain the application and data in one file. This makes it very convenient to distribute the entire application to another user, who can run it in disconnected environments. Microsoft Access offers parameterized queries. These...

Words: 39505 - Pages: 159

Database Sql

...Matharu, Rajdeep Unit 1 True/False Questions 1) At its simplest, a database can be seen as a collection of related data. - True 2) A delimited file is a file where the length of the file is limited. - False 3) A delimited file is one in which the data is separated by a character such as a comma. - True 4) All fixed length files are of the same length. - False 5) In relational databases, logical design is kept separate from physical design. - True 6) A hierarchical database is designed with a tree-like structure that resembles a file system. - True 7) Codd defined the core principles of a relational database in 1970. - True 8) Data redundancy improves the integrity of a database. - False 9) In a diagram, crows feet notation conveys more information about the relationships. - True 10) A primary key uniquely identifies each row in a database table. - True 11) In a relational database, all data is kept in tables, even the information about the tables. - True 12) SQL is the language used by relational databases to create objects and to manipulate and retrieve data. - True 13) An entity is someone who is interested in the database. - False 14) In a statement of scope, a constraint is a limit on what the database will do. - True 15) Oracle is a popular relational database management system. 16) A relational database management system does not include tools for backing up and restoring databases. 17)......

Words: 307 - Pages: 2

Sql Coding

...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...

Words: 377 - Pages: 2

File.

..., the development of the markets will be held back. Five areas of weakness are recognized, namely: • Duplication of clearing and settlement infrastructure at DSE and CSE; • Lack of DVP for indirect market participants; • The need for participants to provide cash and securities 2 days ahead of settlement date; • The payment of cash dividends from issuers to end investors, rather than flowing through the depository, causing delay, uncertainty and inefficiency; and • Lack of 'straight through processing' (STP), leading to manual rekeying of orders and file trans‐mission, and increasing operational risk. 2.3. Types of the financial market in Bangladesh The financial market in Bangladesh is mainly of following types: * 2.3.1. Money Market The primary money market is comprised of banks, FIs and primary dealers as intermediaries and savings & lending instruments, treasury bills as instruments. There are currently 15 primary dealers (12 banks and 3 FIs) in Bangladesh. The only active secondary market is overnight call money market which is participated by the scheduled banks and FIs. The money market in Bangladesh is regulated by Bangladesh Bank (BB), the Central Bank of Bangladesh. * 2.3.2. Capital Market The primary segment of capital market is operated through private and public offering of equity and bond instruments. The secondary segment of capital market is institutionalized by two (02) stock exchanges-Dhaka Stock Exchange and Chittagong Stock Exchange.......

Words: 2216 - Pages: 9

Sql Book

... to the pages in the text where the command is covered. Appendix B: How Do I Reference Appendix B provides students with an opportunity to ask a question, such as “How do I delete rows?”, and to identify the appropriate section in Appendix A to use to find the answer. Appendix B is extremely valuable when students know what task they want to accomplish but can’t remember the exact SQL command they need. Appendix C: Answers to Odd-Numbered Review Questions Answers to the odd-numbered Review Questions in each chapter appear in this appendix so students can make sure that they are completing the Review Questions correctly. GENERAL NOTES TO THE STUDENT You can download the databases used in this text from www.cengage.com. The Access data files for this book include three Access 2003 databases (Premiere Products.mdb, Henry Books.mdb, and Alexamara Marina Group.mdb), which you can open in Access 2000, 2002, or 2003; and three Access 2007 databases (Premiere Products.accdb, Henry Books.accdb, and Alexamara Marina Group.accdb), which you can open in Access 2007. The data files also include script files for Oracle and SQL Server that you can use to create or drop the Premiere Products, Henry Books, and Alexamara Marina Group databases. The script files saved in the Oracle folder have the following functions: Oracle-Alexamara.sql: Creates all the tables in the Alexamara Marina Group database and adds all the data. Run this script file to create the Alexamara Marina Group...

Words: 48772 - Pages: 196

File

...Discovery Exercises – Chapter 4 1. How can you create a file called history by using a redirection operator? >history 2. Wait one minute or more and then change the timestamp on the history file you just created. touch history 3. Back up the history file to the file history.bak. history >history.bak 4. Sort the corp_phones1 file by the last four digits of the phone number. sort –n –k3 corp_phones1 5. Create and use a command that displays only the last names and telephone numbers (omitting the area code) of people in the corp_phones2 file. Place a space between the telephone number and the last name. cut –c 5-20 corp_phone1>corp_phones2 6. Assume you have a subdirectory named datafiles directly under your current working directory. What command can you use to copy the data1 and data2 files from your current working directory to the datafiles directory? cp data1 data2 ./datafiles 7. Assume you have four files: accounts1, accounts2, accounts3, and accounts4. Write the paste command that combines these files and separates the fields on each line with a “/” character, displaying the results to the screen. paste –d / accounts1 accounts2 accounts3 accounts4 8. How would you perform the action in Discovery Exercise 7, but write the results to the file total_accounts? paste –d / accounts1 accounts2 accounts3 accounts4 >total_accounts 9. Assume you have 10 subdirectories and you want to locate all files that end...

Words: 662 - Pages: 3

Sql Quiz

...Chapter 15 Quiz * Due Mar 26 at 6pm * Points 10 * Questions 10 * Available Mar 19 at 12am - Mar 26 at 6pm 8 days * Time Limit None Attempt History   | Attempt | Time | Score | LATEST | Attempt 1 | 36 minutes | 7 out of 10 | Score for this quiz: 7 out of 10 Submitted Mar 19 at 11:58pm This attempt took 36 minutes.   Question 1 1 / 1 pts Parameters for stored procedures and functions can be of any valid SQL Server data type except Parameters for stored procedures and functions can be of any valid SQL Server data type except   date/time Correct!   table   xml   numeric   Question 2 0 / 1 pts Skip to question text. Code a statement that calls the following stored procedure and passes the values ‘2011-12-01’ and 122 to its input parameters. Pass the parameters by name in the reverse order that they’re declared in the procedure. CREATE PROC spInvoiceTotal1        @DateVar smalldatetime,        @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar; ______________________________________________________________________________ You Answered Correct Answers EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01'; EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01'   Question 3 0 / 1 pts Before you can pass a table to a stored procedure or a function as a parameter, you must use the ______________________ statement...

Words: 645 - Pages: 3

File

... consultant. Department of any product or service to the exclusion of others that may be suitable. The United States Department of Agriculture (USDA), prohibits discrimination in all its programs and activities on the basis of race, color, national origin, gender, religion, age, disability, political beliefs, sexual orientation, and marital or family status. (Not all prohibited bases apply to all programs.) Persons with disabilities who Additional single copies of this document may be ordered from: USDA Forest Service Missoula Technology and Development Center 5785 Hwy. 10 West Missoula, MT 59808-9361 Phone: 406–329–3978 Fax: 406–329–3719 Internet: wo_mtdc_pubs@fs.fed.us For additional technical information, contact Wes Throop at the address above. Phone: 406–329–3957 Fax: 406–329–3719 Internet: wthroop@fs.fed.us Lotus Notes: Wesley Throop/WO/ USDAFS An electronic copy of this document is available on the Forest Service’s FSWeb Intranet at: http://fsweb.mtdc.wo.fs.fed.us require alternative means for communication of program information (Braille, large print, audiotape, and so forth) should phone USDA’s TARGET Center at (202) 720-2600 (voice and TDD). To file a complaint of discrimination, write: USDA, Director, Office of Civil Rights, Room 326-W, Whitten Building, 14th and Independence Avenue SW, Washington, DC 20250-9410, or call (202) 720-5964 (voice or TDD). USDA is an equal opportunity provider and employer. ...

Words: 1965 - Pages: 8

Sql Assignment

...SQL> select bdate,address 2 from employee 3 where fname='John' and minit='B' and lname='Smith'; BDATE ADDRESS -------- ------------------------------ 65/01/09 731 Fondren, Houston, TX SQL> select fname,lname,address 2 from employee,department 3 where dname='Research' and 4 dnumber=dno; FNAME LNAME ADDRESS ------------------------------ ------------------------------ ------------------------------ John Smith 731 Fondren, Houston, TX Franklin Wong 638 Voss, Houton, TX Ramesh Narayan 975 Fire Oak, Humble, TX Joyco English 5631 Rice, Houston, TX SQL> select pnumber,dnum,lname,address,bdate 2 from project,department,employee 3 where dnum=dnumber and Mgr_ssn=ssn and plocation='Stafford'; PNUMBER DNUM LNAME ADDRESS BDATE ---------- ---------- ------------------------------ ------------------------------ -------- 10 4 Wallace 291 Berry, Bellaire, TX 41/06/20 30 4 Wallace 291 Berry, Bellaire, TX 41/06/20 SQL> select fname, employee.lname, address 2 from employee, department 3 where department.dname='research' and...

Words: 2277 - Pages: 10

Sql & Qbe

...SQL & QBE There are three typical operations that are done in querying databases (relational algebra): (1) Project—select columns [SELECT in SQL] (2) Restrict—select rows [WHERE] (3) Join—select columns and merge on rows that meet conditions [FROM] & [WHERE] Relational algebra is not used in current systems. It is a conceptual/theoretical way to manipulate RDBs. Structured Query Language (SQL) is a widely used language that retrieves and updates data in tables and views (manipulate RDBs). QBE is a user interface that simplifies SQL procedures. Other than some minor syntax differences, SQL is standardized. It is very powerful—i.e. you can do almost anything with data tables that you want. It is also simple to use. SQL is set based—returns a subset of tables referenced. Action queries enable user to change, insert, create, and delete data sets (tables). Selection queries retrieve and display data. Parameter queries prompt for input information. Dynasets are temporary tables that Access uses to store data resulting from a query. Tables must be related if used in a query. Natural join (equijoin or inner join)—most common kind of join. Two tables are joined on the common (join) column. The WHERE (=) statement specifies the join column(s) in which the rows have to match. Outer join (full)—all rows from both tables are included in output table (left and right outer joins would include all rows in one table but only the matches from the other...

Words: 1410 - Pages: 6

Sql Injection.

...ABSTRACT This term paper discusses the security exposures of a server that occur due to a SQL injection flaw in a web application that communicate with a database. Over ten years have passed since a famous hacker coined the term “SQL injection” and it is still considered one of the major application threats. A lot has been said on this vulnerability, but not all of the aspects and implications have been uncovered, yet. This paper aim is to collate some of the existing knowledge, introduce new techniques and demonstrate how to get complete control over the database management system's underlying operating system, file system and internal network through SQL injection vulnerability in over-looked and theoretically not exploitable scenarios. This paper also discuss about the prevention from the SQL Injection, not only in ORACLE but also in PHP, C#, JAVA and other languages. INDEX ABSTRACT………………………………………………………………………………….....02 INTRODUCTION……………….…………………………….…….………………………….04 BLIND SQL INJECTION…………………………………….………………………………..05 SQL INJECTION OVERVIEW…………………………….………………………………....06 CATEGORIES OF SQL INJECTION ATTACKS…………………………………………..07 WHAT’S VULNERABLE…………………………………………………………..…………08 WHAT’S NOT VULNERABLE…………………………………………………….………….08 SQL INJECTION METHODS……………………………………….……………….……….09 SQL MANIPULATION………………………………………………………..……………….09 CODE INJECTION……………………………………………………….……………………10 FUNCTION CALL INJECTION……………………………………………………………….11 BUFFER OVERFLOWS...

Words: 3449 - Pages: 14

Sql Plus

... Database The roles of the two products are very straightforward. Oracle9i Database manages all your data. This is not just the object relational data that you expect an enterprise database to manage. It can also be unstructured data like: • Spreadsheets • Word documents • PowerPoint presentations • XML • Multimedia data types like MP3, graphics, video, and more The data does not even have to be in the database. Oracle9i Database has services through which you can store metadata about information stored in file systems. You can use the database server to manage and serve information wherever it is located. Instructor Note XML (the Extensible Markup Language) was first ratified by the W3C (World Wide Web Consortium) as the standard for information exchange on the Internet in February 1998. Since then it has been rapidly gaining momentum as the development community has begun to appreciate its potential and as vendors have started to deliver tools to support it. XML specifies a rigorous, textbased way to represent the structure inherent in data so that it can be authored and interpreted unambiguously. Introduction to Oracle9i: SQL I-6 Relational and Object Relational Database Management System • • • • • Relational model and object relational model User-defined data types and objects Fully compatible with relational database Support of multimedia and large objects High-quality database server features I-7...

Words: 33197 - Pages: 133