enkele oefeningen

Question

-- Oefening 1

select familienaam,voornaam,indienst
from tblWerknemers as a
where YEAR(a.InDienst)='1993'

-- Oefening 2

select Klantnummer,Naam,Gemeente
from tblKlanten as a
where Gemeente=N'Leuven'

-- Oefening 3

select Familienaam,Voornaam,Geslacht
from tblWerknemers as a
where Geslacht=2

-- Oefening 4

select Familienaam,Voornaam,Geslacht, gemeente
from tblWerknemers as a
where Geslacht=2 and Gemeente=N'Leuven'

-- Oefening 5

select Klantnummer,Naam,saldo
from tblKlanten as a
where saldo>175
order by Naam

-- Oefening 6

select Klantnummer,Naam,saldo,Ondernemingsnr
from tblKlanten as a
where not a.Ondernemingsnr is null

-- Oefening 7

select COUNT(a.OrderID)
from tblOrders as a
where Orderdatum='20060806'

-- Oefening 8

select a.Productnummer,Productnaam,SUM(b.Hoeveelheid) as [aantal verkocht]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer) inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam


-- Oefening 9

- meest verkocht

select top(1) a.Productnummer,Productnaam,SUM(b.Hoeveelheid) as [aantal verkocht]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer) inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam
order by SUM(b.Hoeveelheid) desc

-- minst verkocht

select top(1) a.Productnummer,Productnaam,SUM(b.Hoeveelheid) as [aantal verkocht]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer) inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam
order by SUM(b.Hoeveelheid) 

-- Oefening 10

select a.Familienaam, COUNT(a.Familienaam)
from tblwerknemers as a 
group by a.Familienaam
having (COUNT(a.Familienaam)>1)
order by a.Familienaam


-- Oefening 10 met klanten

select a.Naam, COUNT(a.naam)
from tblKlanten as a 
group by a.naam
having (COUNT(a.naam)>1)
order by a.Naam

-- Oefening 11

select a.Gemeente,COUNT(a.klantnummer)
from tblKlanten as a 
group by a.gemeente
having COUNT(a.klantnummer)=1


-- Oefening 12

select distinct gemeente
from tblWerknemers

-- Oefening 13

select a.Productnummer,a.Productnaam,SUM(b.Hoeveelheid)
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
inner join tblOrders as c on (b.OrderID=c.OrderID)
where YEAR(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam
having SUM(b.Hoeveelheid)>10

-- Oefening 14

select a.Gemeente,a.Straat
from tblKlanten as a
where Gemeente=N'Kessel-Lo'

-- in betekenis bevat straat

select a.Gemeente,a.Straat
from tblKlanten as a
where Gemeente=N'Kessel-Lo' and Straat like N'%straat%'

-- aantal

select a.Gemeente,COUNT(klantnummer)
from tblKlanten as a
where Gemeente=N'Kessel-Lo' and Straat like N'%straat%'
group by Gemeente

-- Oefening 15

select distinct a.Productnaam
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005


-- Oefening 16

select count(distinct a.Productnaam) as [aantal verschillende]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
inner join tblOrders as c on (b.OrderID=c.OrderID) 
inner join tblKlanten as d on (d.Klantnummer=c.Klantnummer)
where d.Naam=N'Levenslang'

-- Oefening 17

select distinct b.categorienaam
from tblProducten as a join tblCategorieën as b on (a.categorienummer=b.categorienummer) join tblOrderinformatie as c 
on (a.productnummer=c.productnummer) join tblOrders as d on (c.OrderID=d.orderid)
where d.orderid=11066


-- Oefening 18

select a.Categorienaam,COUNT(b.Productnummer)
from tblCategorieën as a inner join tblProducten as b on (a.Categorienummer=b.Categorienummer)
group by a.Categorienaam


-- Oefening 19

select Familienaam,Voornaam,postcode,gemeente
from tblWerknemers


 -- Oefening 20
 
 select Productnaam,PrijsPerEenheid
 from tblProducten as a
 where PrijsPerEenheid>12.5

 -- Oefening 21

select a.Bedrijf,a.Plaats
from tblLeveranciers as a
where a.Plaats=N'Londen'

-- Oefening 22

select a.NederlandseNaam,a.PrijsPerEenheid,b.Categorienaam
from tblProducten as a inner join tblCategorieën as b on (a.Categorienummer=b.Categorienummer)
order by a.NederlandseNaam



---------------------------------------------------------------------------------------------


-- 1	Wat is het adres van de klant die het order met nummer 11013 gaf?

select a.Naam, a.Straat,a.Postnr,a.Gemeente
from tblKlanten as a inner join tblorders as b on (a.klantnummer=b.klantnummer)
where b.OrderID=11013

-- met subquery
select a.Naam, a.Straat,a.Postnr,a.Gemeente
from tblKlanten as a 
where a.Klantnummer =
	(select b.klantnummer 
	from tblOrders as b
	where b.OrderID=11013)

--2	Zijn er klanten met een saldo hoger dan 123 EUR die een order geplaatst hebben?

select distinct a.Klantnummer 
from tblOrders as a
where a.Klantnummer in
	(
	select b.Klantnummer
	from tblKlanten as b
	where b.Saldo>123
	)
	
-- 3	In welke gemeente wonen de meeste klanten?

select a.gemeente, count(a.gemeente)
from tblKlanten as a
group by a.gemeente
having count(a.gemeente)=
	(select top(1) count(b.gemeente)
	from tblKlanten as b
	group by b.Gemeente
	order by count(b.gemeente) desc)
	
-- 4	Geef de factuurbedragen per order gegeven in 2005. (winstpercentage van 30 % per product)

select a.OrderID,sum(a.Hoeveelheid*b.PrijsPerEenheid*(1-a.Korting)*(1.30)) as factuurbedrag
from tblOrderinformatie as a 
inner join tblProducten as b on (a.Productnummer=b.Productnummer)
where a.orderid in
	(select c.orderid
	from tblorders as c
	where YEAR(c.orderdatum) =2005)
group by a.OrderID

-- 5	Wat was de totale omzet van Artemis in 2005? Houd rekening met een winstpercentage van 30 % per product.

select sum(a.Hoeveelheid*b.PrijsPerEenheid*(1-a.Korting)*(1.30)) as factuurbedrag
from tblOrderinformatie as a 
inner join tblProducten as b on (a.Productnummer=b.Productnummer)
where a.orderid in
	(select c.orderid
	from tblorders as c
	where YEAR(c.orderdatum) =2005) 

--6	Wat was de totale omzet in 2005 per product?

select a.Productnummer,a.Productnaam,sum(b.Hoeveelheid*a.PrijsPerEenheid*(1-b.Korting)*(1.30))
from tblProducten as a inner join tblorderinformatie as b on (a.Productnummer=b.Productnummer)
where b.orderid in
	(select c.orderid
	from tblorders as c
	where YEAR(c.orderdatum) =2005) 
group by a.Productnummer,a.Productnaam

-- 7	Bereken de commissie per vertegenwoordiger voor 2005. Deze bedraagt 5 % van de gerealiseerde omzet.

select a.WerknemerID,a.Familienaam,a.Voornaam,sum(c.Hoeveelheid*d.PrijsPerEenheid*(1-c.Korting)*(1.30))*0.05 as commissie
from tblWerknemers as a 
inner join tblOrders as b on (a.WerknemerID=b.WerknemerID)
inner join tblOrderinformatie as c on (b.OrderID=c.OrderID)
inner join tblProducten as d on (c.Productnummer=d.Productnummer)
where year(b.Orderdatum)  =2005
group by a.WerknemerID,a.Familienaam,a.Voornaam


-- 8	Bereken de omzet per productcategorie voor 2005.

select a.Categorienummer,a.Categorienaam,round(SUM(b.PrijsPerEenheid*1.3*c.Hoeveelheid*(1-c.korting)),2) as [omzet per categorie]
from tblCategorieën as a join tblProducten as b on (a.Categorienummer=b.Categorienummer)
join tblOrderinformatie as c on (b.Productnummer=c.Productnummer)
where c.orderid in (
		select d.orderid
		from tblOrders as d
		where year(d.Orderdatum)=2005)
group by a.Categorienummer,a.Categorienaam

-- 9	Bereken de omzet per productcategorie, per vertegenwoordiger voor 2005.

select e.WerknemerID,e.Familienaam,e.Voornaam,a.Categorienummer,a.Categorienaam,round(SUM(b.PrijsPerEenheid*1.3*c.Hoeveelheid*(1-c.korting)),2) as [omzet per categorie]
from tblCategorieën as a join tblProducten as b on (a.Categorienummer=b.Categorienummer)
join tblOrderinformatie as c on (b.Productnummer=c.Productnummer)
join tblOrders as d on (d.orderid=c.orderid)
join tblWerknemers as e on (d.WerknemerID=e.WerknemerID)
where year(d.Orderdatum)=2005
group by a.Categorienummer,a.Categorienaam,e.WerknemerID,e.Familienaam,e.Voornaam

-- 10	Welk product is het meest en welk het minst verkocht in 2005?

-- meest

select top(1) a.Productnummer,a.Productnaam,SUM(b.hoeveelheid)as aantal
from tblProducten as a join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
where b.OrderID in (select c.orderid from tblOrders as c where YEAR(c.orderdatum)=2005)
group by a.Productnummer,a.Productnaam
order by aantal desc

select top(1) a.Productnummer,a.Productnaam,sum(b.Hoeveelheid) as besteld
from tblproducten as a inner join tblOrderinformatie as b 
on (a.Productnummer=b.Productnummer)
where b.OrderID in
	(
	select c.orderid 
	from tblOrders as c
	where YEAR(c.Orderdatum)=2005
	)
group by a.Productnummer,a.Productnaam
order by besteld desc


-- minst

select top(1) a.Productnummer,a.Productnaam,SUM(b.hoeveelheid)as aantal
from tblProducten as a join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
where b.OrderID in (select c.orderid from tblOrders as c where YEAR(c.orderdatum)=2005)
group by a.Productnummer,a.Productnaam
order by aantal 

select top(1) a.Productnummer,a.Productnaam,sum(b.Hoeveelheid) as besteld
from tblproducten as a inner join tblOrderinformatie as b 
on (a.Productnummer=b.Productnummer)
where b.OrderID in
	(
	select c.orderid 
	from tblOrders as c
	where YEAR(c.Orderdatum)=2005
	)
group by a.Productnummer,a.Productnaam
order by besteld 

--11	Wie was de beste klant (hoogste aankoopbedrag) in 2005?

select top(1) a.Klantnummer,a.naam,round(SUM(d.PrijsPerEenheid*1.3*c.Hoeveelheid*(1-c.korting)),2) as [omzet]
from tblKlanten as a join tblOrders as b on (b.Klantnummer=a.Klantnummer)
join tblOrderinformatie as c on (c.OrderID=b.OrderID)
join tblProducten as d on (d.Productnummer=c.Productnummer)
where YEAR(b.Orderdatum)=2005
group by a.Klantnummer,a.naam
order by omzet desc
---------------------------------------------------------------------------------------------------
	
	

-- Oefening 1

select familienaam,voornaam,indienst
from tblWerknemers as a
where YEAR(a.InDienst)='1993'

-- Oefening 2

select Klantnummer,Naam,Gemeente
from tblKlanten as a
where Gemeente=N'Leuven'

-- Oefening 3

select Familienaam,Voornaam,Geslacht
from tblWerknemers as a
where Geslacht=2

-- Oefening 4

select Familienaam,Voornaam,Geslacht, gemeente
from tblWerknemers as a
where Geslacht=2 and Gemeente=N'Leuven'

-- Oefening 5

select Klantnummer,Naam,saldo
from tblKlanten as a
where saldo>175
order by Naam

-- Oefening 6

select Klantnummer,Naam,saldo,Ondernemingsnr
from tblKlanten as a
where not a.Ondernemingsnr is null

-- Oefening 7

select COUNT(a.OrderID)
from tblOrders as a
where Orderdatum='20060806'

-- Oefening 8

select a.Productnummer,Productnaam,SUM(b.Hoeveelheid) as [aantal verkocht]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer) inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam


-- Oefening 9

- meest verkocht

select top(1) a.Productnummer,Productnaam,SUM(b.Hoeveelheid) as [aantal verkocht]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer) inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam
order by SUM(b.Hoeveelheid) desc

-- minst verkocht

select top(1) a.Productnummer,Productnaam,SUM(b.Hoeveelheid) as [aantal verkocht]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer) inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam
order by SUM(b.Hoeveelheid) 

-- Oefening 10

select a.Familienaam, COUNT(a.Familienaam)
from tblwerknemers as a 
group by a.Familienaam
having (COUNT(a.Familienaam)>1)
order by a.Familienaam


-- Oefening 10 met klanten

select a.Naam, COUNT(a.naam)
from tblKlanten as a 
group by a.naam
having (COUNT(a.naam)>1)
order by a.Naam

-- Oefening 11

select a.Gemeente,COUNT(a.klantnummer)
from tblKlanten as a 
group by a.gemeente
having COUNT(a.klantnummer)=1


-- Oefening 12

select distinct gemeente
from tblWerknemers

-- Oefening 13

select a.Productnummer,a.Productnaam,SUM(b.Hoeveelheid)
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
inner join tblOrders as c on (b.OrderID=c.OrderID)
where YEAR(c.Orderdatum)=2005
group by a.Productnummer,a.Productnaam
having SUM(b.Hoeveelheid)>10

-- Oefening 14

select a.Gemeente,a.Straat
from tblKlanten as a
where Gemeente=N'Kessel-Lo'

-- in betekenis bevat straat

select a.Gemeente,a.Straat
from tblKlanten as a
where Gemeente=N'Kessel-Lo' and Straat like N'%straat%'

-- aantal

select a.Gemeente,COUNT(klantnummer)
from tblKlanten as a
where Gemeente=N'Kessel-Lo' and Straat like N'%straat%'
group by Gemeente

-- Oefening 15

select distinct a.Productnaam
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
inner join tblOrders as c on (b.OrderID=c.OrderID)
where year(c.Orderdatum)=2005


-- Oefening 16

select count(distinct a.Productnaam) as [aantal verschillende]
from tblProducten as a inner join tblOrderinformatie as b on (a.Productnummer=b.Productnummer)
inner join tblOrders as c on (b.OrderID=c.OrderID) 
inner join tblKlanten as d on (d.Klantnummer=c.Klantnummer)
where d.Naam=N'Levenslang'

-- Oefening 17

select distinct b.categorienaam
from tblProducten as a join tblCategorieën as b on (a.categorienummer=b.categorienummer) join tblOrderinformatie as c 
on (a.productnummer=c.productnummer) join tblOrders as d on (c.OrderID=d.orderid)
where d.orderid=11066


-- Oefening 18

select a.Categorienaam,COUNT(b.Productnummer)
from tblCategorieën as a inner join tblProducten as b on (a.Categorienummer=b.Categorienummer)
group by a.Categorienaam


-- Oefening 19

select Familienaam,Voornaam,postcode,gemeente
from tblWerknemers


 -- Oefening 20
 
 select Productnaam,PrijsPerEenheid
 from tblProducten as a
 where PrijsPerEenheid>12.5

 -- Oefening 21

select a.Bedrijf,a.Plaats
from tblLeveranciers as a
where a.Plaats=N'Londen'

-- Oefening 22

select a.NederlandseNaam,a.PrijsPerEenheid,b.Categorienaam
from tblProducten as a inner join tblCategorieën as b on (a.Categorienummer=b.Categorienummer)
order by a.NederlandseNaam

Answer 1

--1	Voeg de volgende klant (een winkelier) toe:
--Van Isacker, Sutenboslaan 23, 1000 Brussel, BTWnr 456-895-699.

insert into tblKlanten (Naam,Straat,Postnr,Gemeente,Ondernemingsnr,[Type])
values('Van Isacker','Sutenboslaan 23','1000','Brussel','456-895-699','W');


--controle
select * from tblKlanten where Naam='Van Isacker';

--2	Maak een tabel tblZuivel met alle zuivelproducten.

SELECT productnummer,[Leveranciersnummer]
           ,[Categorienummer]
           ,[Productnaam]
           ,[NederlandseNaam]
           ,[HoeveelheidPerEenheid]
           ,[PrijsPerEenheid]
           ,[Voorraad]
           ,[BTWCode]
           ,[InBestelling]
           ,[Bestelpunt]
           ,[UitAssortiment]
INTO tblZuivel
FROM tblproducten
WHERE [categorienummer] = 4

--3	Verwijder alle zuivelproducten uit de tabel tblProducten.

delete from tblProducten
where Categorienummer=4;

--4	Voeg de zuivelproducten die nog in assortiment zijn terug toe aan tblProducten.

set identity_insert tblProducten on;
insert into tblProducten(productnummer,[Leveranciersnummer]
           ,[Categorienummer]
           ,[Productnaam]
           ,[NederlandseNaam]
           ,[HoeveelheidPerEenheid]
           ,[PrijsPerEenheid]
           ,[Voorraad]
           ,[BTWCode]
           ,[InBestelling]
           ,[Bestelpunt]
           ,[UitAssortiment])
select *
 from tblZuivel 
 where UitAssortiment=0

--5	Voeg aan de tabel tblKlanten een veld Voornaam toe.
alter table tblklanten
add voornaam varchar(25) 


--6	Verwijder de kolom met de Nederlandse naam uit tblProducten

alter table tblproducten
drop constraint SSMA_CC$tblProducten$NederlandseNaam$disallow_zero_length;

alter table tblproducten
drop column nederlandsenaam

--7	Maak een tabel met relevante gegevens voor jobstudenten.

CREATE TABLE [dbo].[tbljobstudenten](
	[JobstudentID] [int] IDENTITY(1,1) NOT NULL,
	[Familienaam] [nvarchar](40) NOT NULL,
	[Voornaam] [nvarchar](40) NULL,
	[Adres] [nvarchar](60) NULL,
	[Gemeente] [nvarchar](30) NULL,
	[Postcode] [nvarchar](4) NULL,
	[Telefoonnummer] [nvarchar](12) NULL,
	[Functie] [nvarchar](30) NULL,
	[BrutoWedde] [int] NULL,
	[Superieur] [nvarchar](2) NULL,
	[Toestelnummer] [nvarchar](2) NULL,
	[Auto] [bit] NULL,
	[InDienst] [datetime] NULL,
	[Geboortedatum] [datetime] NULL,
	[Geslacht] [nvarchar](1) NULL,
	[Foto] [varbinary](max) NULL,
	[Bijzonderheden] [nvarchar](max) NULL,
	CONSTRAINT [tblJobstudenten$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[JobstudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Adres$disallow_zero_length] CHECK  ((len([Adres])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Adres$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Bijzonderheden$disallow_zero_length] CHECK  ((len([Bijzonderheden])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Bijzonderheden$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Familienaam$disallow_zero_length] CHECK  ((len([Familienaam])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Familienaam$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Functie$disallow_zero_length] CHECK  ((len([Functie])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Functie$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Gemeente$disallow_zero_length] CHECK  ((len([Gemeente])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Gemeente$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Geslacht$disallow_zero_length] CHECK  ((len([Geslacht])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Geslacht$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Geslacht$validation_rule] CHECK  (([Geslacht]=(1) OR [Geslacht]=(2)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Geslacht$validation_rule]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Postcode$disallow_zero_length] CHECK  ((len([Postcode])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Postcode$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Superieur$disallow_zero_length] CHECK  ((len([Superieur])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Superieur$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Telefoonnummer$disallow_zero_length] CHECK  ((len([Telefoonnummer])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Telefoonnummer$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Toestelnummer$disallow_zero_length] CHECK  ((len([Toestelnummer])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Toestelnummer$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobstudenten$Voornaam$disallow_zero_length] CHECK  ((len([Voornaam])>(0)))
GO

ALTER TABLE [dbo].[tblJobstudenten] CHECK CONSTRAINT [SSMA_CC$tblJobstudenten$Voornaam$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblJobstudenten] ADD  DEFAULT ((0)) FOR [BrutoWedde]
GO

ALTER TABLE [dbo].[tblJobstudenten] ADD  DEFAULT ((1)) FOR [Auto]
GO

ALTER TABLE [dbo].[tblJobstudenten] ADD  DEFAULT (getdate()) FOR [InDienst]
GO