Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HELP PLS!

Author  Topic 

Darez
Starting Member

4 Posts
Posted - 2010-12-07 : 02:05:32
hi people,

i am facing some difficulties with the last 2 statements of my sql.

create database learn;

use learn;

create table customer (customer_name varchar(10) not null unique,customer_street varchar(10) not null,customer_city varchar(10) not null,primary key(customer_name));

insert into customer values('Jones','Main','Harrison');

insert into customer values('Smith','Main','Rye');

insert into customer values('Hayes','Main','Harrison');

insert into customer values('Curry','North','Rye');

insert into customer values('Lindsay','Park','Pittsfield');

insert into customer values('Turner','Putnam','Stamford');

insert into customer values('Williams','Nassau','Princeton');

insert into customer values('Adams','Spring','Pittsfield');

insert into customer values('Johnson','Alma','Palo Alto');

insert into customer values('Glenn','Sand Hill','Woodside');

insert into customer values('Brooks','Senator','Brooklyn');

insert into customer values('Green','Walnut','Stamford');

insert into customer values('Jackson','University','Salt Lake');

insert into customer values('Majeris','First','Rye');

insert into customer values('McBride','Safety','Rye');

create table branch (branch_name varchar(10) not null unique,branch_city varchar(10) not null,branch_asset integer not null,primary key(branch_name));

insert into branch values('Downtown','Brooklyn',900000);

insert into branch values('Redwood','Palo Alto',2100000);

insert into branch values('Perryridge','Horseneck',1700000);

insert into branch values('Mianus','Horseneck',400200);

insert into branch values('Round Hill','Horseneck',8000000);

insert into branch values('Pownal','Bennington',400000);

insert into branch values('North Town','Rye',3700000);

insert into branch values('Brighton','Brooklyn',7000000);

insert into branch values('Central','Rye',400280);

create table loan (loan_number varchar(10) not null unique,loan_amount integer not null,branch_name varchar(10) not null,primary key(loan_number),foreign key(branch_name) references branch(branch_name));

insert into loan values('L-17',1000,'Downtown');

insert into loan values('L-23',2000,'Redwood');

insert into loan values('L-15',1500,'Perryridge');

insert into loan values('L-14',1500,'Downtown');

insert into loan values('L-93',500,'Mianus');

insert into loan values('L-11',900,'Round Hill');

insert into loan values('L-16',1300,'Perryridge');

insert into loan values('L-20',7500,'North Town');

insert into loan values('L-21',570,'Central');

create table account (account_number varchar(10) not null unique,account_balance integer not null,branch_name varchar(10) not null,primary key(account_number),foreign key(branch_name) references branch(branch_name));

insert into account values('A-101',500,'Downtown');

insert into account values('A-215',700,'Mianus');

insert into account values('A-102',400,'Perryridge');

insert into account values('A-305',350,'Round Hill');

insert into account values('A-201',900,'Perryridge');

insert into account values('A-222',700,'Redwood');

insert into account values('A-217',750,'Brighton');

insert into account values('A-333',850,'Central');

insert into account values('A-444',625,'North Town');

create table borrower (customer_name varchar(10) not null,loan_number varchar(10) not null,foreign key(customer_name) references customer(customer_name),foreign key(loan_number) references loan(loan_number));

insert into borrower values('Jones','L-17');

insert into borrower values('Smith','L-23');

insert into borrower values('Hayes','L-15');

insert into borrower values('Jackson','L-14');

insert into borrower values('Curry','L-93');

insert into borrower values('Smith','L-11');

insert into borrower values('Williams','L-17');

insert into borrower values('Adams','L-16');

insert into borrower values('McBride','L-20');

insert into borrower values('Smith','L-21');

create table depositor (customer_name varchar(10) not null,account_number varchar(10) not null,foreign key(customer_name) references customer(customer_name),foreign key(account_number) references account(account_number));

insert into depositor values('Johnson','A-101');

insert into depositor values('Smith','A-215');

insert into depositor values('Hayes','A-102');

insert into depositor values('Hayes','A-101');

insert into depositor values('Turner','A-305');

insert into depositor values('Johnson','A-201');

insert into depositor values('Jones','A-217');

insert into depositor values('Lindsay','A-222');

insert into depositor values('Majeris','A-333');

insert into depositor values('Smith','A-444');

SELECT * FROM customer;

SELECT * FROM branch;

SELECT * FROM loan;

SELECT * FROM account;

SELECT * FROM borrower;

SELECT * FROM depositor;

SELECT distinct customer.customer_name,customer_city FROM customer,loan,borrower WHERE customer.customer_name=borrower.customer_name and loan.loan_number=borrower.loan_number and branch_name='Perryridge';

SELECT account_number FROM account WHERE account_balance between 700 and 900;

SELECT distinct customer.customer_name,customer_city FROM customer,borrower WHERE customer.customer_name=borrower.customer_name;

SELECT distinct customer_name FROM depositor WHERE customer_name IN (SELECT distinct customer_name FROM loan,borrower WHERE loan.loan_number=borrower.loan_number and branch_name='Perryridge');

SELECT distinct customer_name FROM depositor WHERE customer_name NOT IN (SELECT distinct customer_name FROM loan,borrower WHERE loan.loan_number=borrower.loan_number and branch_name='Perryridge');

but it keeps on say my last 2 query has error near somewhere in the bracket... can somebody tell me why?

Darez
Starting Member

4 Posts
Posted - 2010-12-07 : 02:18:41
to add on, the purpose of the last 2 statements (queries) of my sql is to find out the names of customers with both accounts and loans at Perryridge branch and the names of customers with an account but not a loan at Perryridge branch.

Hope you guys can help me on this... I felt so frustrated that I cannot get this thing right... urgh
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-12-07 : 02:20:52
Are you sure about error ?
Because I am not getting any error through.

Vaibhav T

If I cant go back, I want to go fast...
Go to Top of Page

Darez
Starting Member

4 Posts
Posted - 2010-12-07 : 02:24:49
u are able to generate results for the last 2 queries that i entered?

i can't!

i'm using MySQL 3.23.54, is that version too old and that's why i have problems?
Go to Top of Page

Darez
Starting Member

4 Posts
Posted - 2010-12-07 : 02:40:00
this is rather urgent... hope somebody can explain to me... thanks a million!
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-12-07 : 02:41:16
I Could not understood "my sql" is "MySQL".
Thats why.
As I thought this is SQL Server Query.
In SQL Server I Can able to generate results for the last 2 queries.

This is SQL Server Forum.

Vaibhav T

If I cant go back, I want to go fast...
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-12-07 : 02:43:32
Go to MySQL Forum will be better to get the answer fast.
http://forums.mysql.com/

Vaibhav T

If I cant go back, I want to go fast...
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-12-07 : 02:46:15
This is a Microsoft SQL Server forum. If you have questions on MySQL, which is a totally different database product with different syntax, try asking on the MySQL forums. http://forums.mysql.com or http://www.dbforums.com

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site