how to write queries in SQL - Software Development

3 New Member

--
-- Table structure for table borrower
--
DROP TABLE IF EXISTS borrower;
CREATE TABLE borrower (
brw_num int(11) NOT NULL default '0',
brw_lname varchar(15) default NULL,
brw_fname varchar(15) default NULL,
brw_initial varchar(1) default NULL,
brw_areacode varchar(3) default NULL,
brw_phone varchar(8) default NULL,
PRIMARY KEY (brw_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table borrower
--
INSERT INTO borrower VALUES
(1001,'Ramas',' Alfred','A','61 5','844-2573'),
(1002,'Dunne',' Leona','K','713 ','894-1238'),
(1003,'Smith',' Kathy','W','615 ','894-2285'),
(1004,'Olowski' ,'Paul','F','61 5','894-2180'),
(1005,'Orlando' ,'Myron',NULL,' 615','222-1672'),
(1006,'Brian',' Amy','B','713', '442-3381'),
(1007,'Brown',' James','G','615 ','297-1228'),
(1008,'Williams ','George',NULL ,'615','290-2556'),
(1009,'Farriss' ,'Anne','G','71 3','382-7185'),
(1010,'Smith',' Olette','K','61 5','297-3809');

--
-- Table structure for table movie
--
DROP TABLE IF EXISTS movie;
CREATE TABLE movie (
movie_code varchar(10) NOT NULL,
movie_copies int(11) default '0',
movie_name varchar(50) default NULL,
movie_charge decimal(8,2) default '0.00',
movie_late_chg_ day decimal(8,2) default '0.00',
PRIMARY KEY (movie_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table movie
--
INSERT INTO movie VALUES
('M3456',3,'Ram blin Tulip','3.50',' 0.25'),
('R2345',2,'Onc e Upon a Midnight Breezy','4.0000 ','0.25'),
('S4567',3,'Tul ips and Threelips','3.0 0','0.25'),
('W1234',5,'Bri ght Stars and Doodle Berries','4.00' ,'0.50');

--
-- Table structure for table copy
--
DROP TABLE IF EXISTS copy;
CREATE TABLE copy (
copy_code varchar(10) NOT NULL,
copy_num int(11) default '0' ,
movie_code varchar(10) default NULL,
PRIMARY KEY (copy_code),
FOREIGN KEY (movie_code) REFERENCES movie(movie_cod e)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table copy
--
INSERT INTO copy VALUES
('M3456-1',1,'M3456'),( 'M3456-2',2,'M3456'),( 'M3456-3',3,'M3456'),
('R2345-1',1,'R2345'),( 'R2345-2',2,'R2345'),( 'S4567-1',1,'S4567'),
('S4567-2',2,'S4567'),( 'S4567-3',3,'S4567'),( 'W1234-1',1,'W1234'),
('W1234-2',2,'W1234'),( 'W1234-3',3,'W1234'),( 'W1234-4',4,'W1234'),
('W1234-5',5,'W1234');

--
-- Table structure for table rental
--

DROP TABLE IF EXISTS rental;
CREATE TABLE rental (
rent_num int(11) NOT NULL auto_increment,
brw_num int(11) default '0',
rent_charge decimal(8,2) default '0.00',
rent_date datetime default NULL,
PRIMARY KEY (rent_num),
FOREIGN KEY (brw_num) REFERENCES borrower(brw_nu m)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table rental
--
INSERT INTO rental VALUES
(1,1002,'7.50', '2004-03-15 00:00:00'),
(2,1003,'4.00', '2004-03-15 00:00:00'),
(3,1001,'4.00', '2004-03-15 00:00:00'),
(4,1004,'4.00', '2004-03-15 00:00:00'),
(5,1004,'4.00', '2004-03-16 00:00:00'),
(6,1001,'7.00', '2004-03-16 00:00:00'),
(7,1003,'4.00', '2004-03-16 00:00:00'),
(8,1002,'4.00', '2004-03-17 00:00:00'),
(9,1001,'4.00', '2004-03-17 00:00:00'),
(10,1004,'4.00' ,'2004-03-17 00:00:00'),
(11,1002,'4.00' ,'2004-03-18 00:00:00'),
(12,1003,'3.50' ,'2004-03-18 00:00:00'),
(13,1001,'4.00' ,'2004-03-18 00:00:00'),
(14,1004,'11.00 ','2004-03-18 00:00:00'),
(15,1004,'4.00' ,'2004-03-19 00:00:00');

--
-- Table structure for table rent_line
--

DROP TABLE IF EXISTS rent_line;
CREATE TABLE rent_line (
rent_num int(11) NOT NULL default '0',
rentline_num int(11) NOT NULL default '0',
copy_code varchar(10) default NULL,
rentline_charge decimal(8,2) default '0.00',
rentline_late_c hg_day decimal(8,2) default '0.00',
rentline_date_o ut datetime default NULL,
rentline_date_d ue datetime default NULL,
rentline_date_i n datetime default NULL,
rentline_days_l ate int(11) default '0',
rentline_overdu e_chg decimal(8,2) default '0.00',
PRIMARY KEY (rent_num,rentl ine_num),
FOREIGN KEY (copy_code) REFERENCES copy(copy_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table rent_line
--
INSERT INTO rent_line VALUES
(1,1,'M3456-3','3.50','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(1,2,'W1234-2','4.00','0.50 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(2,1,'M3456-2','3.50','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(3,1,'R2345-1','4.00','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00',NULL, 0,'0.00'),
(4,1,'R2345-2','4.00','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(5,1,'W1234-1','4.00','0.50 ','2004-03-16 00:00:00','2004-03-18 00:00:00','2004-03-18 00:00:00',0,'0. 00'),
(6,1,'S4567-2','3.00','0.25 ','2004-03-16 00:00:00','2004-03-18 00:00:00',NULL, 0,'0.00'),
(6,2,'W1234-4','4.00','0.50 ','2004-03-16 00:00:00','2004-03-18 00:00:00','2004-03-18 00:00:00',0,'0. 00'),
(7,1,'W1234-5','4.00','0.50 ','2004-03-16 00:00:00','2004-03-18 00:00:00','2004-03-18 00:00:00',0,'0. 00'),
(8,1,'R2345-1','4.00','0.25 ','2004-03-17 00:00:00','2004-03-19 00:00:00','2004-03-19 00:00:00',0,'0. 00'),
(9,1,'W1234-1','4.00','0.50 ','2004-03-17 00:00:00','2004-03-19 00:00:00','2004-03-19 00:00:00',0,'0. 00'),
(10,1,'R2345-2','4.00','0.25 ','2004-03-17 00:00:00','2004-03-19 00:00:00','2004-03-19 00:00:00',0,'0. 00'),
(11,1,'W1234-2','4.00','0.50 ','2004-03-18 00:00:00','2004-03-20 00:00:00',NULL, 0,'0.00'),
(12,1,'M3456-2','3.50','0.25 ','2004-03-18 00:00:00','2004-03-20 00:00:00',NULL, 0,'0.00'),
(13,1,'W1234-3','4.00','0.50 ','2004-03-18 00:00:00','2004-03-20 00:00:00','2004-03-22 00:00:00',2,'1. 00'),
(14,1,'S4567-2','3.00','0.25 ','2004-03-18 00:00:00','2004-03-20 00:00:00','2004-03-21 00:00:00',1,'0. 25'),
(14,2,'W1234-4','4.00','0.50 ','2004-03-18 00:00:00','2004-03-20 00:00:00',NULL, 0,'0.00'),
(14,3,'R2345-2','4.00','0.25 ','2004-03-18 00:00:00','2004-03-20 00:00:00','2004-03-22 00:00:00',2,'0. 50'),
(15,1,'W1234-2','4.00','0.50 ','2004-03-19 00:00:00','2004-03-21 00:00:00','2004-03-22 00:00:00',1,'0. 50');

how do i write sql queries for the followin problems

1 List the number and names of all borrowers that have not borrowed any movies

2 List the code(s), name(s), and the price of the most expensive movies.

thanks

May 17 '10 #1

Subscribe Reply

1677

Jim Doherty

897

Recognized Expert Contributor

@islandgal
If this were an MS SQL Server database it would be something like this each question answered in order

Expand|Select|Wrap|Line Numbers

 SELECT     dbo.borrower.brw_num, dbo.borrower.brw_lname

FROM         dbo.borrower LEFT OUTER JOIN

                      dbo.rental ON dbo.borrower.brw_num = dbo.rental.brw_num

WHERE     (dbo.rental.brw_num IS NULL)

Expand|Select|Wrap|Line Numbers

 SELECT     TOP 100 PERCENT movie_name, movie_charge

FROM         dbo.movie

ORDER BY movie_charge DESC

May 17 '10 #2

islandgal

New Member

thanks Jim,

i cannot execute the first query...there is an error

"Syntax error in Join operation"

the second works fine!!!!

thanks

May 17 '10 #3

Similar topics

6788

Bad performance in queries with Jet4.0 and linked ODBC-tables to SQL-Server 2000

by: Andreas Lauffer | last post by:

I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:

Microsoft SQL Server

3722

Mutually exclusive counts on ordered queries

by: WertmanTheMad | last post by:

Ive been playing with this for a few days and thought I might thow it out for seggestions. I have Several Queries that need counts returned The Queries are Mutually Exclusive meaning whatever Query they return in first they cannot be included in the counts of any queries below them. This set of queries for example

Microsoft SQL Server

1391

Why are the "same" queries optimised differently ?

by: PaulR | last post by:

We have seen this a lot, but have just experienced the opposite to what we have always seen previously, so this has prompted me to ask a high level - why do we get this behaviour? If we re-write queries in the following pattern (simplified) select ... from table1 t1 inner join table2 t2 on (t1.pk = t2.fk) inner join table3 t3 on (t3.fk = t2.pk)

DB2 Database

1386

Book recommendation for access queries?

by: Turloch O'Tierney | last post by:

Greetings, I am migrating Access queries, is there a book that people recommend, ideally with close to EBNF (concise) SQL Query description Is the 'Access 2003 Bible' a good choice, or should I just rely on msdn? Thank you for your time,

Microsoft Access / VBA

21643

pass-through queries and parameters

by: Zlatko Matiæ | last post by:

Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the server ? Is it possible to use parameters in pass-through queries ? An additional question: Is it possible to connect to a database on MySQL or PostgreSQL using ADO ? Is it possible to execute pass-through queries with parameters, using ADO...

Microsoft Access / VBA

2730

Question on Queries

by: Kurch | last post by:

Hello, I have an Access file saved on my company server. Is it possible to allow selected people within my network to access a query that I've written and allow them to read and write changes to the cells in this query and not be able to read/write data in the master table?

Microsoft Access / VBA

3485

LongestRunningQueries.vbs - Using a VB Script to show long-running queries, complete with query plans

by: Aaron West | last post by:

Try this script to see what queries are taking over a second. To get some real output, you need a long-running query. Here's one (estimated to take over an hour): PRINT GETDATE() select count_big(*) from sys.objects s1, sys.objects s2, sys.objects s3, sys.objects s4, sys.objects s5 PRINT GETDATE()

Microsoft SQL Server

1691

Alternative way to write queries: Parameterized queries .... i guess

by: weird0 | last post by:

i write query in this manner by contatenting a string: cmd.CommandText = "SELECT acc_name FROM Account_Information where user_id='" + User_Id + "'"; Is there any other way to do it.... please tell me how to do so and some good link related to that?

C# / C Sharp

8447

Queries in VBA,ADO

by: iheartvba | last post by:

Hi, I am trying to figure out how to write queries using ADO For example if I want to Select (Field) From (Table) Where (Field) = (TextBox in Form) and just open that query as a recordset. I am getting the following error: RunTime error 3709 The Connection Cannot be used to perform this operation. It is either closed or invalid in this context. This is my code:

Microsoft Access / VBA

3330

Its very very urgent Please....Could you please help.plz help in solving queries

by: jenipriya | last post by:

Hi all... its very urgent.. please........i m a beginner in oracle.... Anyone please help me wit dese codes i hv tried... and correct the errors... The table structures i hav Employee (EmpID, EmpName,DeptID,DateOfJoin, Sal, Addr) Finance (EmpID, Sal) Club (Clubname, EmpID, Fee, DateOfJoin) Leave (EmpID, Date) Department (DeptID, DeptName, NoOfEmployees)...

Oracle Database

10364

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10109

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

9186

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6876

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5545

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5678

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4328

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3849

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

3008

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General